Now there was this issue from one the developers when they are trying to connect to a database through the linked server it errors out. I tried to login and fire the same query it ran so I asked him to check again with the proper credential. He was using his windows login which was part of some windows domain group which I was unable to resolve. So I asked him to use the login the application will actually use. He tried that but there was no lock. Now I looked into the configuration of the linked server it was set to use the login's current security content. User was trying to run a proc which was calling a table in the linked server. So while he tried to execute this he forgot to check if the login actually also existed in the linked server as well. I identified that the login in the linked server was missing. We changed the security content to use a paricular login for the linked server to fix this.
Tushar
Tuesday, September 20, 2011
Tuesday, September 6, 2011
T-Replication 101
Well there have been a lot of articles for starting with transactional-replication. But there have been rarely an article for fixing the common issues one runs into. That's the motivation for this article. Actually I had also run into these issues a couple of times but used to drop the whole replication and bring it up again. But then one fine day I decided to find out the basics and try to help others on this.
After configuring the distribution, publication and subscription when you start the replication monitor you may encounter the below mentioned error.
Error authenticating proxy tushar-hub\lifercks, system error: Logon failure: unknown user name or bad password.). The step failed.
Well the reason may be you have put a windows account with some wrong password. Well the immediate fix can serve as you go to the agent job and in the step('Run agent') where the agent starts; use the 'Run As' in the property and change to run under the sql server service account. This should fix the issue. I am presuming here that your sql server agent service account is running under a domain account with all security checks.
http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/42229a2c-a9ef-4082-806c-3381e9f6d510
The next most common error will be the replication is failing.
Error :
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)
The issue is some application/user updated/deleted/inserted some row directly in the subscriber. Then when there is a change in publisher and the distributor tries to push those changes into subscriber the error shows up as there is inconsistancy between subscriber and publisher.
To fix this there are a couple of steps we have to follow.
1. To find the sequence number and then the articles involved in the mismatch.
2. Find the data miss match
Now to find the lsn number we will use the query mentioned below.
select * from distribution..MSrepl_errors with(nolock) where time> '20110921' order by time desc
here '20110921' is the time stamp from when we suspect the error has occured.
Now when we have the sequence number we fire the next query to identify the objects which are in suspect.
exec distribution..sp_browsereplcmds @xact_seqno_start='0x00023C6800000BA3000C00000000', @xact_seqno_end='0x00023C6800000BA3000C00000000'
here 0x00023C6800000BA3000C00000000 is the lsn number.
Now since we have the objects we can use the table diff utility to find the actual data issues we have
to be continued.
After configuring the distribution, publication and subscription when you start the replication monitor you may encounter the below mentioned error.
Error authenticating proxy tushar-hub\lifercks, system error: Logon failure: unknown user name or bad password.). The step failed.
Well the reason may be you have put a windows account with some wrong password. Well the immediate fix can serve as you go to the agent job and in the step('Run agent') where the agent starts; use the 'Run As' in the property and change to run under the sql server service account. This should fix the issue. I am presuming here that your sql server agent service account is running under a domain account with all security checks.
http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/42229a2c-a9ef-4082-806c-3381e9f6d510
The next most common error will be the replication is failing.
Error :
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)
The issue is some application/user updated/deleted/inserted some row directly in the subscriber. Then when there is a change in publisher and the distributor tries to push those changes into subscriber the error shows up as there is inconsistancy between subscriber and publisher.
To fix this there are a couple of steps we have to follow.
1. To find the sequence number and then the articles involved in the mismatch.
2. Find the data miss match
Now to find the lsn number we will use the query mentioned below.
select * from distribution..MSrepl_errors with(nolock) where time> '20110921' order by time desc
here '20110921' is the time stamp from when we suspect the error has occured.
Now when we have the sequence number we fire the next query to identify the objects which are in suspect.
exec distribution..sp_browsereplcmds @xact_seqno_start='0x00023C6800000BA3000C00000000', @xact_seqno_end='0x00023C6800000BA3000C00000000'
here 0x00023C6800000BA3000C00000000 is the lsn number.
Now since we have the objects we can use the table diff utility to find the actual data issues we have
to be continued.
Thursday, August 25, 2011
Lets Start with Kid's Stuff..
How to delete a login which has users mapped to different databases?
Well you can drop a login to which database users are mapped but this will create orphaned users.
Well to begin with we have to know the name of the login name from syslogins table in case you are not sure abpout the login name.
query : select name from syslogins where name like '%%'
Now we got the login name to begin with and then we can get all the user databases the login has access to with help of sp_helplogins
query: Exec sp_helplogins 'loginName'
Now we need to delete the user from the different databases.
We will use the sp_dropuser to delete the user from the databases.
query : EXEC revokedbaccess ''
remeber this name is the username in the database corresponding to the login.
Now after deleting all users from all the databases. We need to delete the login as well. You can do that from the GUI or use the script.
script: Exec sp_droplogin ''
References: MSDN
Tushar
Well you can drop a login to which database users are mapped but this will create orphaned users.
Well to begin with we have to know the name of the login name from syslogins table in case you are not sure abpout the login name.
query : select name from syslogins where name like '%
Now we got the login name to begin with and then we can get all the user databases the login has access to with help of sp_helplogins
query: Exec sp_helplogins 'loginName'
Now we need to delete the user from the different databases.
We will use the sp_dropuser to delete the user from the databases.
query : EXEC revokedbaccess '
remeber this name is the username in the database corresponding to the login.
Now after deleting all users from all the databases. We need to delete the login as well. You can do that from the GUI or use the script.
script: Exec sp_droplogin '
References: MSDN
Tushar
Monday, July 25, 2011
Virtual Log Active and Inactive
Hi,
This is a script which will help to look up the active and inactive virtual log file count.
CREATE TABLE #eachDB(FileID INT, FileSize BIGINT, StartOffset BIGINT, FSeqNo BIGINT
, [Status] BIGINT, Parity BIGINT, CreateLSN NUMERIC(38))
CREATE TABLE #summary(Database_Name sysname, VLF INT, VLF_Count INT, Log_File_ID INT)
EXEC sp_MSforeachdb N'Use [?];
Insert Into #eachDB
Exec sp_executesql N''DBCC LogInfo(?)'';
Insert Into #summary
Select DB_Name(),Status,Count( Status), FileID
From #eachDB
group by Status,FileID
Truncate Table #eachDB;'
SELECT Database_Name,Case When VLF=2 Then 'Active' else 'InActive' End as Virtual_log,VLF_Count,Log_File_ID
FROM #summary
where Database_Name not in ('model','master','tempdb','msdb')
ORDER BY Database_Name,Log_File_ID ;
DROP TABLE #eachDB;
DROP TABLE #summary;
Tushar
This is a script which will help to look up the active and inactive virtual log file count.
CREATE TABLE #eachDB(FileID INT, FileSize BIGINT, StartOffset BIGINT, FSeqNo BIGINT
, [Status] BIGINT, Parity BIGINT, CreateLSN NUMERIC(38))
CREATE TABLE #summary(Database_Name sysname, VLF INT, VLF_Count INT, Log_File_ID INT)
EXEC sp_MSforeachdb N'Use [?];
Insert Into #eachDB
Exec sp_executesql N''DBCC LogInfo(?)'';
Insert Into #summary
Select DB_Name(),Status,Count( Status), FileID
From #eachDB
group by Status,FileID
Truncate Table #eachDB;'
SELECT Database_Name,Case When VLF=2 Then 'Active' else 'InActive' End as Virtual_log,VLF_Count,Log_File_ID
FROM #summary
where Database_Name not in ('model','master','tempdb','msdb')
ORDER BY Database_Name,Log_File_ID ;
DROP TABLE #eachDB;
DROP TABLE #summary;
Tushar
Generating Script for re-building and re-organising indexes
Hi,
This script will help to prepare the script for re-building or re-oragnising index based on 30% default fragment index.
-- USE DB
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
-- EXEC (@command); -- the execute command is commented here..
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
reference: sqlservercentral.com
Tushar
This script will help to prepare the script for re-building or re-oragnising index based on 30% default fragment index.
-- USE DB
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
-- EXEC (@command); -- the execute command is commented here..
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
reference: sqlservercentral.com
Tushar
Listing Jobs names and Schedule
Hi,
This is a script to detail the job names and schedule.
Script:
use msdb
Select Job.job_name, Job.category,
CASE
WHEN Job.enabled=0 THEN 'INACTIVE'
WHEN Job.enabled=1 THEN 'ACTIVE'
END AS STATUS
,D.schedule
from
(select j.name as job_name,c.name as category,j.enabled ,j.job_id
from sysjobs j
join syscategories c on c.category_id=j.category_id
) as Job
join sysjobschedules sc on sc.job_id = Job.job_id
join
(SELECT s.schedule_id,
s.[name],
CASE
WHEN s.freq_type = 0x1
THEN
'Once on '
+ CONVERT (
CHAR (10),
CAST (CAST (s.active_start_date AS VARCHAR) AS DATETIME),
102)
WHEN s.freq_type = 0x4 AND s.freq_interval > 0
THEN
CASE
WHEN s.freq_interval > 1
THEN
'Every ' + CAST (s.freq_interval AS VARCHAR) + ' days'
ELSE
'Every day'
END
WHEN s.freq_type = 0x8
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Weekly on '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' weeks on '
END
+ LEFT (
CASE
WHEN s.freq_interval & 1 = 1 THEN 'Sunday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 2 = 2 THEN 'Monday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 32 = 32 THEN 'Friday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '
ELSE ''
END,
LEN (
CASE
WHEN s.freq_interval & 1 = 1 THEN 'Sunday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 2 = 2 THEN 'Monday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 32 = 32 THEN 'Friday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '
ELSE ''
END)
- 1)
WHEN s.freq_type = 0x10
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' months on the '
END
+ CAST (s.freq_interval AS VARCHAR)
+ CASE
WHEN s.freq_interval IN (1, 21, 31) THEN 'st'
WHEN s.freq_interval IN (2, 22) THEN 'nd'
WHEN s.freq_interval IN (3, 23) THEN 'rd'
ELSE 'th'
END
WHEN s.freq_type = 0x20
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' months on the '
END
+ CASE s.freq_relative_interval
WHEN 0x01 THEN 'first '
WHEN 0x02 THEN 'second '
WHEN 0x04 THEN 'third '
WHEN 0x08 THEN 'fourth '
WHEN 0x10 THEN 'last '
END
+ CASE s.freq_interval
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'day'
WHEN 9 THEN 'week day'
WHEN 10 THEN 'weekend day'
END
WHEN s.freq_type = 0x40
THEN
'Automatically starts when SQLServerAgent starts.'
WHEN s.freq_type = 0x80
THEN
'Starts whenever the CPUs become idle'
ELSE
''
END
+ CASE
WHEN s.freq_subday_type = 0x1 OR s.freq_type = 0x1
THEN
' at '
+ LEFT (s.active_start_time, 2)
+ ':'
+ substring (s.active_start_time, 3, 2)
+ ':'
+ right (s.active_start_time, 2)
WHEN s.freq_subday_type IN (0x2, 0x4, 0x8)
THEN
' every '
+ CAST (s.freq_subday_interval AS VARCHAR)
+ CASE freq_subday_type
WHEN 0x2 THEN ' second'
WHEN 0x4 THEN ' minute'
WHEN 0x8 THEN ' hour'
END
+ CASE WHEN s.freq_subday_interval > 1 THEN 's'
ELSE ' '
END
ELSE
' '
END
+ CASE
WHEN s.freq_subday_type IN (0x2, 0x4, 0x8)
THEN
' between '
+ LEFT (s.active_start_time, 2)
+ ':'
+ substring (s.active_start_time, 3, 2)
+ ':'
+ right (s.active_start_time, 2)
+ ' and '
+ LEFT (s.active_end_time, 2)
+ ':'
+ substring (s.active_end_time, 3, 2)
+ ':'
+ right (s.active_end_time, 2)
ELSE
''
END
AS schedule
FROM (
SELECT schedule_id,
[name],
freq_type,
freq_interval,
freq_subday_type,
freq_subday_interval,
freq_relative_interval,
freq_recurrence_factor,
active_start_date,
active_end_date,
REPLICATE ('0', 6 - len (cast (active_start_time AS VARCHAR)))
+ cast (active_start_time AS VARCHAR)
AS active_start_time,
REPLICATE ('0', 6 - len (cast (active_end_time AS VARCHAR)))
+ cast (active_end_time AS VARCHAR)
AS active_end_time,
date_created,
date_modified,
version_number
FROM msdb.dbo.sysschedules) AS s ) as D on D.schedule_id=sc.schedule_id
Refernence: sqlservercentral.com
Tushar
This is a script to detail the job names and schedule.
Script:
use msdb
Select Job.job_name, Job.category,
CASE
WHEN Job.enabled=0 THEN 'INACTIVE'
WHEN Job.enabled=1 THEN 'ACTIVE'
END AS STATUS
,D.schedule
from
(select j.name as job_name,c.name as category,j.enabled ,j.job_id
from sysjobs j
join syscategories c on c.category_id=j.category_id
) as Job
join sysjobschedules sc on sc.job_id = Job.job_id
join
(SELECT s.schedule_id,
s.[name],
CASE
WHEN s.freq_type = 0x1
THEN
'Once on '
+ CONVERT (
CHAR (10),
CAST (CAST (s.active_start_date AS VARCHAR) AS DATETIME),
102)
WHEN s.freq_type = 0x4 AND s.freq_interval > 0
THEN
CASE
WHEN s.freq_interval > 1
THEN
'Every ' + CAST (s.freq_interval AS VARCHAR) + ' days'
ELSE
'Every day'
END
WHEN s.freq_type = 0x8
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Weekly on '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' weeks on '
END
+ LEFT (
CASE
WHEN s.freq_interval & 1 = 1 THEN 'Sunday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 2 = 2 THEN 'Monday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 32 = 32 THEN 'Friday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '
ELSE ''
END,
LEN (
CASE
WHEN s.freq_interval & 1 = 1 THEN 'Sunday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 2 = 2 THEN 'Monday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 32 = 32 THEN 'Friday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '
ELSE ''
END)
- 1)
WHEN s.freq_type = 0x10
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' months on the '
END
+ CAST (s.freq_interval AS VARCHAR)
+ CASE
WHEN s.freq_interval IN (1, 21, 31) THEN 'st'
WHEN s.freq_interval IN (2, 22) THEN 'nd'
WHEN s.freq_interval IN (3, 23) THEN 'rd'
ELSE 'th'
END
WHEN s.freq_type = 0x20
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' months on the '
END
+ CASE s.freq_relative_interval
WHEN 0x01 THEN 'first '
WHEN 0x02 THEN 'second '
WHEN 0x04 THEN 'third '
WHEN 0x08 THEN 'fourth '
WHEN 0x10 THEN 'last '
END
+ CASE s.freq_interval
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'day'
WHEN 9 THEN 'week day'
WHEN 10 THEN 'weekend day'
END
WHEN s.freq_type = 0x40
THEN
'Automatically starts when SQLServerAgent starts.'
WHEN s.freq_type = 0x80
THEN
'Starts whenever the CPUs become idle'
ELSE
''
END
+ CASE
WHEN s.freq_subday_type = 0x1 OR s.freq_type = 0x1
THEN
' at '
+ LEFT (s.active_start_time, 2)
+ ':'
+ substring (s.active_start_time, 3, 2)
+ ':'
+ right (s.active_start_time, 2)
WHEN s.freq_subday_type IN (0x2, 0x4, 0x8)
THEN
' every '
+ CAST (s.freq_subday_interval AS VARCHAR)
+ CASE freq_subday_type
WHEN 0x2 THEN ' second'
WHEN 0x4 THEN ' minute'
WHEN 0x8 THEN ' hour'
END
+ CASE WHEN s.freq_subday_interval > 1 THEN 's'
ELSE ' '
END
ELSE
' '
END
+ CASE
WHEN s.freq_subday_type IN (0x2, 0x4, 0x8)
THEN
' between '
+ LEFT (s.active_start_time, 2)
+ ':'
+ substring (s.active_start_time, 3, 2)
+ ':'
+ right (s.active_start_time, 2)
+ ' and '
+ LEFT (s.active_end_time, 2)
+ ':'
+ substring (s.active_end_time, 3, 2)
+ ':'
+ right (s.active_end_time, 2)
ELSE
''
END
AS schedule
FROM (
SELECT schedule_id,
[name],
freq_type,
freq_interval,
freq_subday_type,
freq_subday_interval,
freq_relative_interval,
freq_recurrence_factor,
active_start_date,
active_end_date,
REPLICATE ('0', 6 - len (cast (active_start_time AS VARCHAR)))
+ cast (active_start_time AS VARCHAR)
AS active_start_time,
REPLICATE ('0', 6 - len (cast (active_end_time AS VARCHAR)))
+ cast (active_end_time AS VARCHAR)
AS active_end_time,
date_created,
date_modified,
version_number
FROM msdb.dbo.sysschedules) AS s ) as D on D.schedule_id=sc.schedule_id
Refernence: sqlservercentral.com
Tushar
Thursday, July 7, 2011
When was Server Last Restarted
There was a report that we had to put together for the servers that had re-started within the tenure and the probable reason for the restart.
Below query gives you the time the SQL Server Reatrted. This query assumes that the tempdb is created everytime the SQL Server restarts.
SELECT [Name] , CrDate as DateServerRebooted
from sys.sysdatabases
WHERE dbid = 2
Now if you want to further when the SQL Server was stopped and the probable reason for the Server shutdown you have to go to the individual error logs and identify.
Tushar
Below query gives you the time the SQL Server Reatrted. This query assumes that the tempdb is created everytime the SQL Server restarts.
SELECT [Name] , CrDate as DateServerRebooted
from sys.sysdatabases
WHERE dbid = 2
Now if you want to further when the SQL Server was stopped and the probable reason for the Server shutdown you have to go to the individual error logs and identify.
Tushar
Subscribe to:
Posts (Atom)