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'
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Thursday, October 22, 2015
Friday, October 2, 2015
Configuring Dbmail
--check for operators
use msdb
Go
select * from msdb.dbo.sysoperators
--add operators
use msdb
Go
exec dbo.sp_add_operator @name= User1, @enabled = 1, @email_address = 'user1e@abc.com'
exec dbo.sp_add_operator @name= 'User2, @enabled = 1, @email_address = 'user2@abc.com'
--enable the database mail
USE master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO
--Set up account and profile.
--One last thing but most important thing left is Letting the SQL Server Agent Talk to Database Mail. If we do not perform this step we get the following error
Avoiding 'Failed to notify <operator> via email' errors
use msdb
Go
select * from msdb.dbo.sysoperators
--add operators
use msdb
Go
exec dbo.sp_add_operator @name= User1, @enabled = 1, @email_address = 'user1e@abc.com'
exec dbo.sp_add_operator @name= 'User2, @enabled = 1, @email_address = 'user2@abc.com'
--enable the database mail
USE master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO
--Set up account and profile.
--One last thing but most important thing left is Letting the SQL Server Agent Talk to Database Mail. If we do not perform this step we get the following error
Avoiding 'Failed to notify <operator> via email' errors
- -Highlight the "SQL Server Agent" in the object Explorer
- -Go to Properties
- -Go to Alert system options
- - Check the enable profile on and select the profile
Error: Failed to notify via email
The above error occurs when we forget to perform the step to let the SQL Server Agent Talk to Database Mail
To perform that , follow the following steps -
-Highlight the "SQL Server Agent" in the object Explorer
-Go to Properties
-Go to Alert System options
- Check the enable profile on and select the profile in the mail Session section.
To perform that , follow the following steps -
-Highlight the "SQL Server Agent" in the object Explorer
-Go to Properties
-Go to Alert System options
- Check the enable profile on and select the profile in the mail Session section.
Friday, August 14, 2015
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
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
Friday, July 17, 2015
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
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 ;
- 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
"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
Labels:
Administration,
error,
offline,
stress at work,
work adventure,
work issue
Thursday, June 25, 2015
Create and restore database using backup files from the network
I had a project where I had to create and restore the database from the backup files in a network.
I achieved it by following these steps.
First of all need to make sure that the 'xp_cmdshell' is enabled.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO
I ran the following query to see the physical path of the back up sets that was moved over from the network.
restore filelistonly from disk V:\SQL\Backup\Daily\DB1_backup_2015_06_22_194001_8960340.bak'
with file = 1
go
[In SQL Server 2008 and later, you need to have CREATE DATABASE permission to get this information
If there are multiple backup sets on the backup device, specify the backup set number using the “WITH FILE = n” option.
To specify the second backup set on the device run this code:
restore filelistonly from disk V:\SQL\Backup\Daily\DB1_backup_2015_06_22_194001_8960340.bak'
with file = 2
go ]
Script 1 :
Important!!!!
BEFORE RUNNING MAKE SURE THAT THE SERVICE ACCOUNT HAS PERMISSION IN THE PATH DIR.
--To create a backupfiles list and populate a table in the work db
--delete from HCFS_WORKDB..DBBackUpFilesList
CREATE TABLE #DirectoryList (
line VARCHAR(512))
DECLARE @f table (Fname varchar(100))
DECLARE @Path varchar(256) = 'dir V:\SQL\Backup\Daily\'
DECLARE @Command varchar(1024) = @Path+ '/b /a-d /s *.bak ' --' /s /b /o:gn' --/a:d /s /b | sort'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE Line IS NULL
delete from #DirectoryList where ( line not like ('%2015_06_24%')) --keep the recent backup file
delete from #DirectoryList where line like '%system%'
delete from #DirectoryList where line like '%\Diffferntial%'
delete from #DirectoryList where line not like '%\client_%'
--select line from #DirectoryList
insert into @f
select SUBSTRING(line,charindex('\client_',line,1)+1,512 ) from #DirectoryList
--capture the database name and the filename in the table
insert into DB_WORKDB..DBBackUpFilesList (DBname,BackupfileName)
select rtrim(left(SUBSTRING(fname ,charindex('\client_',fname,1)+1,100 ),charindex('_backup',SUBSTRING(fname ,charindex('\client_',fname,1)+2,100 ),1))),SUBSTRING(fname ,charindex('\client_',fname,1)+1,100 ) from @f
--select substring(SUBSTRING(line,charindex('\client_',line,1)+1,512 ),charindex('\',line,1)+1,512) from #DirectoryList
DROP TABLE #DirectoryList
GO
-------------------------------------------------------------------------------
Script 2 : Create multiple databases using a cursor
declare @curClientCode as cursor ,@db varchar(100),
@sqlCreateDb nvarchar(max),
@dbName varchar(100)
SET @curClientCode = CURSOR FOR
--crete db for arm clients too
select name from sys.databases
where name like 'client_%'
OPEN @curClientCode
FETCH NEXT
FROM @curClientCode into @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = cast(@db as varchar)
SET @sqlCreateDb = 'USE master' + CHAR(13) +
--'DROP DATABASE ' + @dbName + CHAR(13) +
' CREATE DATABASE ' + @dbName + CHAR(13) +
' ON (NAME = ' + @dbName + '_data, FILENAME = ''E:\SQL\Data\' + @dbName +'.mdf'', Size = 100MB,MAXSIZE=unlimited, FILEGROWTH = 10)' + char(13) +
' LOG ON (NAME = ' + @dbName + '_log, FILENAME = ''L:\SQL\Data\' + @dbName +'.ldf'', Size = 5MB,MAXSIZE=500MB, FILEGROWTH = 10); '
print @sqlCreateDb
exec sp_executesql @sqlCreateDb;
FETCH NEXT
FROM @curClientCode into @db
END
CLOSE @curClientCode
DEALLOCATE @curClientCode
---------------------------------------------------------
Script 3 : Restore the database with MOVE command from the file
The backup files name were found from the table in the DB_workdb that was used earlier to capture the database name and the backup files
use master
Go
declare @curClientCode as cursor ,@db varchar(10),
@sqlRestoreDb nvarchar(max),
@dbName varchar(20),
@backupFile varchar(100)
SET @curClientCode = CURSOR FOR
select name from sys.databases
where name like 'client_%'
OPEN @curClientCode
FETCH NEXT
FROM @curClientCode into @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = @db
SELECT @backupFile = BackupFileName from HCFS_WORKDB..dbBackUpFilesList where dbname = @dbName
SET @sqlRestoreDb = 'ALTER DATABASE ' + @dbName + char(13) +
' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' + char(13) + char(13)
SET @sqlRestoreDb = @sqlRestoreDb + ' RESTORE DATABASE ' + @dbName + char(13) +
' FROM DISK = ''V:\SQL\Backup\Daily\' + @dbName + '\'+ + @backupFile + ''''+ char(13)+
' WITH MOVE N''' + @dbName + ''' TO N''E:\SQL\Data\' + @dbName + '.mdf'',' + char(13)+
' MOVE N''' + @dbName + '_log'' TO N''L:\SQL\Data\' + @dbName + '.ldf'',' + char(13)+
' REPLACE, STATS = 10 ' + char(13)
SET @sqlRestoreDb = @sqlRestoreDb + 'ALTER DATABASE ' + @dbName + char(13) +
' SET MULTI_USER WITH ROLLBACK IMMEDIATE ' + char(13)
print @sqlRestoreDb
exec sp_executesql @sqlRestoreDb;
FETCH NEXT
FROM @curClientCode into @db
END
CLOSE @curClientCode
DEALLOCATE @curClientCode
GO
I achieved it by following these steps.
- I moved all the backup files from the network to the local server in a backup folder.
- Created a table BackupFileList to store the current database name and the backup file names using the command shell script
- Executed a dynamic SQL script to create all the databases.
- Executed the dynamic script to restore all the databases I made use of the created table to build a dynamic query to find the database name and the backup file.
First of all need to make sure that the 'xp_cmdshell' is enabled.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO
I ran the following query to see the physical path of the back up sets that was moved over from the network.
restore filelistonly from disk V:\SQL\Backup\Daily\DB1_backup_2015_06_22_194001_8960340.bak'
with file = 1
go
[In SQL Server 2008 and later, you need to have CREATE DATABASE permission to get this information
If there are multiple backup sets on the backup device, specify the backup set number using the “WITH FILE = n” option.
To specify the second backup set on the device run this code:
restore filelistonly from disk V:\SQL\Backup\Daily\DB1_backup_2015_06_22_194001_8960340.bak'
with file = 2
go ]
Script 1 :
Important!!!!
BEFORE RUNNING MAKE SURE THAT THE SERVICE ACCOUNT HAS PERMISSION IN THE PATH DIR.
--To create a backupfiles list and populate a table in the work db
--delete from HCFS_WORKDB..DBBackUpFilesList
CREATE TABLE #DirectoryList (
line VARCHAR(512))
DECLARE @f table (Fname varchar(100))
DECLARE @Path varchar(256) = 'dir V:\SQL\Backup\Daily\'
DECLARE @Command varchar(1024) = @Path+ '/b /a-d /s *.bak ' --' /s /b /o:gn' --/a:d /s /b | sort'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE Line IS NULL
delete from #DirectoryList where ( line not like ('%2015_06_24%')) --keep the recent backup file
delete from #DirectoryList where line like '%system%'
delete from #DirectoryList where line like '%\Diffferntial%'
delete from #DirectoryList where line not like '%\client_%'
--select line from #DirectoryList
insert into @f
select SUBSTRING(line,charindex('\client_',line,1)+1,512 ) from #DirectoryList
--capture the database name and the filename in the table
insert into DB_WORKDB..DBBackUpFilesList (DBname,BackupfileName)
select rtrim(left(SUBSTRING(fname ,charindex('\client_',fname,1)+1,100 ),charindex('_backup',SUBSTRING(fname ,charindex('\client_',fname,1)+2,100 ),1))),SUBSTRING(fname ,charindex('\client_',fname,1)+1,100 ) from @f
--select substring(SUBSTRING(line,charindex('\client_',line,1)+1,512 ),charindex('\',line,1)+1,512) from #DirectoryList
DROP TABLE #DirectoryList
GO
-------------------------------------------------------------------------------
Script 2 : Create multiple databases using a cursor
declare @curClientCode as cursor ,@db varchar(100),
@sqlCreateDb nvarchar(max),
@dbName varchar(100)
SET @curClientCode = CURSOR FOR
--crete db for arm clients too
select name from sys.databases
where name like 'client_%'
OPEN @curClientCode
FETCH NEXT
FROM @curClientCode into @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = cast(@db as varchar)
SET @sqlCreateDb = 'USE master' + CHAR(13) +
--'DROP DATABASE ' + @dbName + CHAR(13) +
' CREATE DATABASE ' + @dbName + CHAR(13) +
' ON (NAME = ' + @dbName + '_data, FILENAME = ''E:\SQL\Data\' + @dbName +'.mdf'', Size = 100MB,MAXSIZE=unlimited, FILEGROWTH = 10)' + char(13) +
' LOG ON (NAME = ' + @dbName + '_log, FILENAME = ''L:\SQL\Data\' + @dbName +'.ldf'', Size = 5MB,MAXSIZE=500MB, FILEGROWTH = 10); '
print @sqlCreateDb
exec sp_executesql @sqlCreateDb;
FETCH NEXT
FROM @curClientCode into @db
END
CLOSE @curClientCode
DEALLOCATE @curClientCode
---------------------------------------------------------
Script 3 : Restore the database with MOVE command from the file
The backup files name were found from the table in the DB_workdb that was used earlier to capture the database name and the backup files
use master
Go
declare @curClientCode as cursor ,@db varchar(10),
@sqlRestoreDb nvarchar(max),
@dbName varchar(20),
@backupFile varchar(100)
SET @curClientCode = CURSOR FOR
select name from sys.databases
where name like 'client_%'
OPEN @curClientCode
FETCH NEXT
FROM @curClientCode into @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = @db
SELECT @backupFile = BackupFileName from HCFS_WORKDB..dbBackUpFilesList where dbname = @dbName
SET @sqlRestoreDb = 'ALTER DATABASE ' + @dbName + char(13) +
' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' + char(13) + char(13)
SET @sqlRestoreDb = @sqlRestoreDb + ' RESTORE DATABASE ' + @dbName + char(13) +
' FROM DISK = ''V:\SQL\Backup\Daily\' + @dbName + '\'+ + @backupFile + ''''+ char(13)+
' WITH MOVE N''' + @dbName + ''' TO N''E:\SQL\Data\' + @dbName + '.mdf'',' + char(13)+
' MOVE N''' + @dbName + '_log'' TO N''L:\SQL\Data\' + @dbName + '.ldf'',' + char(13)+
' REPLACE, STATS = 10 ' + char(13)
SET @sqlRestoreDb = @sqlRestoreDb + 'ALTER DATABASE ' + @dbName + char(13) +
' SET MULTI_USER WITH ROLLBACK IMMEDIATE ' + char(13)
print @sqlRestoreDb
exec sp_executesql @sqlRestoreDb;
FETCH NEXT
FROM @curClientCode into @db
END
CLOSE @curClientCode
DEALLOCATE @curClientCode
GO
Friday, June 19, 2015
query sql error log
Grab the information of sql error log in a table and query
--gather all the error in a temp table and query the temp table to find the specific error by text search or date range
CREATE TABLE #SQLErrorLog
(
LogDate DATETIME,
ProcessInfo VARCHAR(50),
Text VARCHAR(1500)
)
GO
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 0
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 1
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 2
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 3
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 4
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 5
go
SELECT * FROM #SQLErrorLog
WHERE LogDate >= CONVERT(datetime,'06/08/2015') AND LogDate < CONVERT(datetime,'06/20/2015')
and Text LIKE '%failed%' OR Text LIKE 'Error:%'
ORDER BY LogDate DESC
DROP TABLE #SQLErrorLog
--gather all the error in a temp table and query the temp table to find the specific error by text search or date range
CREATE TABLE #SQLErrorLog
(
LogDate DATETIME,
ProcessInfo VARCHAR(50),
Text VARCHAR(1500)
)
GO
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 0
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 1
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 2
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 3
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 4
go
INSERT INTO #SQLErrorLog
EXEC xp_readerrorlog 5
go
SELECT * FROM #SQLErrorLog
WHERE LogDate >= CONVERT(datetime,'06/08/2015') AND LogDate < CONVERT(datetime,'06/20/2015')
and Text LIKE '%failed%' OR Text LIKE 'Error:%'
ORDER BY LogDate DESC
DROP TABLE #SQLErrorLog
Wednesday, June 10, 2015
script: How to find logical and physical file names of the database
SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName
FROM sys.master_files AS mf
FROM sys.master_files AS mf
Labels:
Administration,
database name,
filename,
master_files,
script,
sys.master_files
Friday, May 29, 2015
Error: Could not continue scan with NOLOCK due to SQL Server data movement
Ran the following
DBCC CHECKDB(MY_DATABASE) WITH PHYSICAL_ONLY
I got table error something like below
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown),
page ID (1:219356) contains an incorrect page ID in its page header.
The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 144115188075921408
So I ran the following
USE [master]
GO
ALTER DATABASE [MY_DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [DATABASE]
GO
DBCC CHECKTABLE(MY-Badtable, 'REPAIR_REBUILD')
GO
ALTER DATABASE [MY_DATABASE] SET MULTI_USER WITH ROLLBACK IMMEDIATE
Then ran the following again
DBCC CHECKDB(MY_DATABASE)
This worked, no errors!
DBCC CHECKDB(MY_DATABASE) WITH PHYSICAL_ONLY
I got table error something like below
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown),
page ID (1:219356) contains an incorrect page ID in its page header.
The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 144115188075921408
So I ran the following
USE [master]
GO
ALTER DATABASE [MY_DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [DATABASE]
GO
DBCC CHECKTABLE(MY-Badtable, 'REPAIR_REBUILD')
GO
ALTER DATABASE [MY_DATABASE] SET MULTI_USER WITH ROLLBACK IMMEDIATE
Then ran the following again
DBCC CHECKDB(MY_DATABASE)
This worked, no errors!
Labels:
Administration,
datafile,
dbcc checkdb,
dbcc checktable,
error
Friday, May 1, 2015
Find database lock
IF EXISTS(SELECT request_session_id FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('Model'))
PRINT 'Model Database being used by some other session'
ELSE
PRINT 'Model Database not used by other session'
---to see what is locking up the database
SELECT request_session_id FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('Model')
Labels:
Administration,
create database,
data,
database lock,
locks,
model,
transaction lock
Thursday, March 19, 2015
Logging the performance counter
The following script creates a table PerfMondata in a SQLMonitor database and logs the counter in the interval of 30 seconds
use [SQLMonitor]
GO
SET NOCOUNT ON ;
DECLARE @PerfCounters TABLE (
[Counter] nvarchar(770),
[CounterType] int,
[FirstValue] decimal (38,2),
[FirstDateTime] datetime,
[SecondValue] decimal(38,2),
[SecondDateTime] datetime ,
[ValueDiff] AS ([SecondValue] - [FirstValue]) ,
[timeDiff] AS (DateDiff(ss,[FirstDateTime],[SecondDateTime])),
[CounterValue] decimal(38,2)
) ;
INSERT INTO @PerfCounters (
[Counter],
[CounterType],
[FirstValue],
[FirstDateTime]
)
SELECT RTRIM([OBJECT_NAME]) + N' : ' + RTRIM([counter_name]) + N':' +RTRIM([instance_name]),[cntr_type],[cntr_value],GETDATE()
from sys.dm_os_performance_counters
WHERE [counter_name] IN ('Page life expectancy','Lazy writes/sec', 'Page reads/sec', 'Page writes/sec ','Page lookups/sec',
'Free list stalls/sec','User Connections','Lock Waits/sec','Number of Deadlocks/sec',
'Transactions/sec ', 'Forwarded Records/sec','Index Searches/sec','Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec',
'Target Server Memory (KB)', 'Latch Waits/sec' ,'Buffer cache hit ratio'
)
order by [OBJECT_NAME] + N' : ' + [counter_name]+ N':' +RTRIM([instance_name])
Waitfor delay '00:00:30';
Update @PerfCounters
SET [SecondValue]= [cntr_value],
[SecondDateTime] = GETDATE()
from sys.dm_os_performance_counters
where [Counter] = RTRIM([OBJECT_NAME]) + N' : ' + RTRIM([counter_name]) + N':' +RTRIM([instance_name])
AND [counter_name] IN ('Page life expectancy','Lazy writes/sec', 'Page reads/sec', 'Page writes/sec ','Page lookups/sec',
'Free list stalls/sec','User Connections','Lock Waits/sec','Number of Deadlocks/sec',
'Transactions/sec ', 'Forwarded Records/sec','Index Searches/sec','Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec',
'Target Server Memory (KB)', 'Latch Waits/sec','Buffer cache hit ratio' )
--for per sec counters
Update @PerfCounters
SET CounterValue = ([SecondValue]-[FirstValue])/(DateDiff(ss,[FirstDateTime],[SecondDateTime]))
where [counterType] = 272696576;
--for non per sec counters
Update @PerfCounters
SET CounterValue = [SecondValue]
where [counterType] <> 272696576;
select * from @PerfCounters
INSERT INTO PerfMondata (perfCounter,Value,CAptureDate)
SELECT [counter],[CounterValue],[SecondDateTime] from @PerfCounters
--select * from PerfMondata
--following stored procedure takes in a counter as the parameter and displays the report on a specific counter
use [SQLMonitor]
GO
if OBJECTPROPERTY(OBJECT_ID('proc_sqlAdmin_PerfMonReport'),'IsProcedure')= 1
DROP PROCEDURE proc_sqlAdmin_PerfMonReport
GO
CREATE PROCEDURE dbo.proc_sqlAdmin_PerfMonReport
(
@Counter nvarchar(128)= '%'
)
AS
BEGIN ;
SELECT [Perfcounter],value,captureDate
from perfMondata
WHERE [Perfcounter] like @Counter
ORDER BY [Perfcounter],[captureDate]
END ;
/*
exec dbo.proc_sqlAdmin_PerfMonReport '%Page life expectancy%'
exec dbo.proc_sqlAdmin_PerfMonReport '%Batch Requests/sec%'
exec dbo.proc_sqlAdmin_PerfMonReport '%Page Writes/sec%'
*/
use [SQLMonitor]
GO
SET NOCOUNT ON ;
DECLARE @PerfCounters TABLE (
[Counter] nvarchar(770),
[CounterType] int,
[FirstValue] decimal (38,2),
[FirstDateTime] datetime,
[SecondValue] decimal(38,2),
[SecondDateTime] datetime ,
[ValueDiff] AS ([SecondValue] - [FirstValue]) ,
[timeDiff] AS (DateDiff(ss,[FirstDateTime],[SecondDateTime])),
[CounterValue] decimal(38,2)
) ;
INSERT INTO @PerfCounters (
[Counter],
[CounterType],
[FirstValue],
[FirstDateTime]
)
SELECT RTRIM([OBJECT_NAME]) + N' : ' + RTRIM([counter_name]) + N':' +RTRIM([instance_name]),[cntr_type],[cntr_value],GETDATE()
from sys.dm_os_performance_counters
WHERE [counter_name] IN ('Page life expectancy','Lazy writes/sec', 'Page reads/sec', 'Page writes/sec ','Page lookups/sec',
'Free list stalls/sec','User Connections','Lock Waits/sec','Number of Deadlocks/sec',
'Transactions/sec ', 'Forwarded Records/sec','Index Searches/sec','Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec',
'Target Server Memory (KB)', 'Latch Waits/sec' ,'Buffer cache hit ratio'
)
order by [OBJECT_NAME] + N' : ' + [counter_name]+ N':' +RTRIM([instance_name])
Waitfor delay '00:00:30';
Update @PerfCounters
SET [SecondValue]= [cntr_value],
[SecondDateTime] = GETDATE()
from sys.dm_os_performance_counters
where [Counter] = RTRIM([OBJECT_NAME]) + N' : ' + RTRIM([counter_name]) + N':' +RTRIM([instance_name])
AND [counter_name] IN ('Page life expectancy','Lazy writes/sec', 'Page reads/sec', 'Page writes/sec ','Page lookups/sec',
'Free list stalls/sec','User Connections','Lock Waits/sec','Number of Deadlocks/sec',
'Transactions/sec ', 'Forwarded Records/sec','Index Searches/sec','Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec',
'Target Server Memory (KB)', 'Latch Waits/sec','Buffer cache hit ratio' )
--for per sec counters
Update @PerfCounters
SET CounterValue = ([SecondValue]-[FirstValue])/(DateDiff(ss,[FirstDateTime],[SecondDateTime]))
where [counterType] = 272696576;
--for non per sec counters
Update @PerfCounters
SET CounterValue = [SecondValue]
where [counterType] <> 272696576;
select * from @PerfCounters
INSERT INTO PerfMondata (perfCounter,Value,CAptureDate)
SELECT [counter],[CounterValue],[SecondDateTime] from @PerfCounters
--select * from PerfMondata
--following stored procedure takes in a counter as the parameter and displays the report on a specific counter
use [SQLMonitor]
GO
if OBJECTPROPERTY(OBJECT_ID('proc_sqlAdmin_PerfMonReport'),'IsProcedure')= 1
DROP PROCEDURE proc_sqlAdmin_PerfMonReport
GO
CREATE PROCEDURE dbo.proc_sqlAdmin_PerfMonReport
(
@Counter nvarchar(128)= '%'
)
AS
BEGIN ;
SELECT [Perfcounter],value,captureDate
from perfMondata
WHERE [Perfcounter] like @Counter
ORDER BY [Perfcounter],[captureDate]
END ;
/*
exec dbo.proc_sqlAdmin_PerfMonReport '%Page life expectancy%'
exec dbo.proc_sqlAdmin_PerfMonReport '%Batch Requests/sec%'
exec dbo.proc_sqlAdmin_PerfMonReport '%Page Writes/sec%'
*/
Subscribe to:
Posts (Atom)