Script: finding the object_id from the page_id

I had a situation where there was a page split ( monitored through Extended Events) and the page_id was 46565645. I wanted to find the table so that I could work with the fill factor of the indexes.   First I turned the following trace ON     DBCC TRACEON (3604); DBCC PAGE (6, 1, 295, 0); DBCC TRACEOFF (3604); GO

 

PAGE: (1:295)   BUFFER:   BUF @0x00000004FD8C7980   bpage = 0x00000004A2D14000          bhash = 0x0000000000000000          bpageno = (1:295) bdbid = 6                           breferences = 0                     bcputicks = 0 bsampleCount = 0                    bUse1 = 55116                       bstat = 0x809 blog = 0x15ab215a                   bnext = 0x0000000000000000            PAGE HEADER:   Page @0x00000004A2D14000   m_pageId = (1:295)                  m_headerVersion = 17                m_type = 17 m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8200 m_objId (AllocUnitId.idObj) = 84    m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594043432960 Metadata: PartitionId = 72057594039042048                                Metadata: IndexId = 0 Metadata: ObjectId = 245575913      m_prevPage = (0:0)                  m_nextPage = (0:0) pminlen = 8008                      m_slotCnt = 1                       m_freeCnt = 83 m_freeData = 8107                   m_reservedCnt = 0                   m_lsn = (35:200:9) m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0 m_tornBits = 1093512791             DB Frag ID = 1                        Allocation Status   GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL                        DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED     In the above result Metadata: ObjectId = 245575913    gave me the object_id   I was able to find the table name with the page splits once I had the object_id .

Work adventure : MoveSQL Agent Jobs to another instanse

It is very easy to move (recreate) the sql agent job that you want to export to another instance of SQL server

1. Highlight the job you want o export
2. Right click
3. Choose Scrip Job as CREATE TO

The script is created and you can run that in the new instance that will create the job.

To move multiple jobs
You can also script multiple jobs to a single file. In SSMS

Click on Jobs,
hit F7 to get the 'Object Explorer Details' tab.
Highlight the jobs you want,
Script job as...

Work Adventure: Fix Orphaned user

When I restored a database to a new server, the users were imported but the login were not. The database users were  imported as the orphan users in SQL 2012

Here is how I found the orphaned users in a database

EXEC sp_change_users_login @Action='Report'

--OR we can run the following in the current database context

select * from sysusers where issqluser = 1 and (sid <> 0x0

and sid is not null)

and (LEN(sid) <= 16 ) AND suser_sname(sid) is null 





-----------------------------------------

The in order to link the orphan user to the existing login  I did the following


sp_change_users_login 'Update_One', 'OrphanUser, 'Logintolinkto'

 

Work Adeventure: Backup database to network

I had a situation where I had to perform a backup of the database on a network. I performed the following

Mapped the  nwMachine\L share to Y drive.
Thus I had (\\nwMachine\L) Y:

If I do a backup at this point the SQL server will not see the mapped drive (Y:) yet .
I had to run few scripts to make SQL server see the mapped Y drive.

EXEC sp_configure 'advanced', 1

RECONFIGURE WITH override

GO

--to enable the xp_cmdshell
--once the job is done make sure this is turned off

EXEC sp_configure 'xp_cmdshell',1

RECONFIGURE WITH override

GO


now make sql server see the mapped drive
EXEC xp_cmdshell 'net use Y: \\nwMachine\L
GO



--EXEC xp_cmdshell 'net use <local mapped drive> (space)  <shared path>
 
--Once we get result as "command ran successfully" we are successful.

--Now in the backup wizard the Y drive should appear along with the other local drives.

--now turn off the xp_cmdshell

EXEC sp_configure 'xp_cmdshell',0

RECONFIGURE WITH override

GO

Work adeventure : Permission to view sql error log (ONLY)

I had a situation where I had to give a user permission to view the sqlerror log only. I achieved by doing the following

• Create a login  DBA_error_logUser  in the server
• Added the login to the securityadmin server role ( to view sql error log the login must be the member of this server role)
• Created a user DBA_error_logUser  with map to the login to grant access to the error logs
• Deny Alter to any Login 
• Grant permission to view Sql Server Logs for the user
• Create a log on Trigger to deny access to Query Window

---Create a login  DBA_error_logUser  in the server

use [master]
GO

CREATE LOGIN [DBA_ErrorLogUser] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO


--Added the login to the securityadmin server role
EXEC master..sp_addsrvrolemember @loginame = N'DBA_ErrorLogUser', @rolename = N'securityadmin'
 GO


--Created a user DBA_error_logUser  with map to the login to grant access to the error logs

CREATE USER [DBA_ErrorLogUser] FOR LOGIN [DBA_ErrorLogUser] GO

--Deny Alter to any Login

DENY ALTER ANY LOGIN TO DBA_ErrorLogUser GO


--Grant permission to view Sql Server Logs for the user

Grant EXECUTE ON master.sys.xp_readerrorlog TO DBA_ErrorLogUser GO

--Create a log on Trigger to deny access to Query Window

  IF EXISTS ( SELECT * FROM master.sys.server_triggers
                WHERE parent_class_desc = 'SERVER' AND name = N'Deny_QueryWindowLogin_Trigger' )

DROP TRIGGER [Deny_QueryWindowLogin_Trigger] ON ALL SERVER
 GO

Create TRIGGER Deny_QueryWindowLogin_Trigger ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON AS
BEGIN
   DECLARE @data XML
   SET @data = EVENTDATA()
  DECLARE @AppName SYSNAME, @LoginName SYSNAME

  SELECT @AppName = [program_name] FROM sys.dm_exec_sessions WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') IF @AppName= 'Microsoft SQL Server Management Studio - Query' AND @LoginName = 'DBA_ErrorLogUser'

BEGIN
             ROLLBACK ; --Disconnect the session
END
 END ;

Work adventure: I/O error 21(The device is not ready )

We had an error all of a sudden on our sql server

"Microsoft OLE DB Provider for SQL Server:
I/O error 21(The device is not ready.) detected during read at offset 0x0000a7a239c000 in file 'DataFileName'.
- HY000 - HRESULT=0x80004005 “

Since the error message mentions that the drive on which one of the data file resided was not ready, it was obvious that the underlying storage was the problem.
But the System Event Log did not have any error messages related to storage. The database was online and accessible. But when I  tried accessing the Properties of this database, the same error was thrown.


The reason for this error was not something that happened at that time. Another email chain from the Windows Administrators sent a few hours ago said it all. They had found that the drive letter associated with the Mount Point hosting this data file was missing and they had *mapped* it somehow. Not sure how they had done the drive mapping. Most likely the drive hosting this data file got disconnected while the database was online.


I fixed this by bringing the database offline and back online after few seconds

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE <dbname> SET ONLINE