Archive
Query Complete SQL error log in one GO !!
Hi All,
this post coming after a long time.
if you need to search a particular word or error code in the SQL error long,then you would have to do it one by one .
i.e .. you will proceed like below (let say for example we are looking for connection timeout errors in Always ON setup, this could be any error or a
particular word you are interested in looking for)
to query the current SQL error log
EXECmaster.dbo.Xp_readerrorlog 0,1,N‘A connection timeout’
0 –> current logs | 1 –> SQL error log (also if you specify nothing in this place .. it is taken as SQL error log by default)
third parameter is something you are searching for in the error log ..
for the previous error log (ie errorlog.1) we have to use EXECmaster.dbo.Xp_readerrorlog 1,1,N‘A connection timeout’
so this becomes tedious over time .. let put this in a loop as below …
assuming you have total of 30 previous long retained ..
use tempdb
go
CREATE TABLE #read_error_log
(
logdate DATETIME,
processinfo VARCHAR(200),
errorlogtext VARCHAR(max)
)
declare @int int= 0
while (@int <= 30)
begin
INSERT INTO #read_error_log
EXEC master.dbo.Xp_readerrorlog @int,1,N’A connection timeout’
set @int= @int+ 1
End
SELECT *
FROM #read_error_log
ORDER BY logdateDESC
–TRUNCATE TABLE #read_error_log
–DROP TABLE #read_error_log
The above query will give you results much faster then query individual error logs ..
Refer below Links for more details on reading error logs ::
http://mssqlwiki.com/tag/sp_readerrorlog-parameters/
http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/
http://strictlysql.blogspot.in/2013/05/xpreaderrorlog-in-sql-server-2012.html
Enjoy 🙂
Running DBCC CHECKDB on all databases at once !!!
Issue ::
some times it so happens .. that the storage assigned to SQL servers may take a sudden
unexpected reboot .. and in those moments we may have LOST WRITES in
databases .. which can cause them to be marked as suspect or causing
issues while accessing objects …
in those moments we have to ensure consistancy and allocation across all DBs
are intact .. running DBCC CheckDB manually on each and every option is time
consuming (plus multiple windows are to be opened)
SCRIPT :: below script will help us run DBCC checkDB on all DBs once we have count of all DBs present on our instance .. (the other option you have to achieve the same is to use CHECKDB job .. but with below script you will be able to view the errors in Query analyser itself)
1> use sp_helpdb .. to check total number of databases present on your instance …
according to the total number of DBs .. use dbid >= * and dbid <= * to cover all DBs
—Script to Run in different windows
—— by commenting and dbid >= * and dbid <= * part in the where clause
—————————————-SCRIPT START ————————————————-
DECLARE @name VARCHAR(50) — database name
DECLARE @SQL VARCHAR(256) — SQL command
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘tempdb’)
and dbid >= 1 and dbid <= 20 —- window 1
–and dbid >= 21 and dbid <= 50 —- uncomment and run from QA window 2
–and dbid >= 51 and dbid <= 100 —- uncomment this and run from QA window 3
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
–SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
—BACKUP DATABASE @name TO DISK = @fileName
set @SQL = ‘DBCC CHECKDB’ + ‘ ‘ + ‘(‘+@name + ‘)’
print @SQL
exec (@SQL)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
—————————————-SCRIPT END————————————————-
you should see the output similar to above ..
go throught the errors and see if Allocation or integrity errors are reported for any DB …
accordingly use Repair_REBUILD or REPAIR_ALLOW_DATA_LOSS of DBCC CheckDB ..
alway prefer to use lastest Good backup over using above repair option !!!!
ENJOY 🙂
Delete OLD SQL Backups using Powershell in one go !!!
issue :
some times the maintance plans do not delete old Backups files (based on their age) ,that we have intended it to do
also the delete maintance task (subtask) may also skip files .. based on their creation date or AGE ..
manually deleting the old .BAK or any other files is not really inspiring after a certain time ..
Solution :
we can use Powershell (PS) to help us acheive the same result .. with few lines ..
SETUP :: im backing up the databases into a test folder .. but the PS script can used to do the same tasks for any storage filer server .. that you must be using to store backups — JUST MAKE SURE YOU PASS the CORRECT PATH ..
use below script to backup all database to a sepcfic path
USE master
GO
DECLARE @int varchar(15)
declare @Rand varchar(15)
DECLARE @SQL varchar(200)
declare @path varchar(200)
DECLARE @Cur varchar(20)
declare curr CURSOR FOR
SELECT name from sys.databases where database_id <> 2
OPEN curr
FETCH NEXT
FROM curr INTO @cur
WHILE @@FETCH_STATUS = 0
BEGIN
–declare @var char(20)
PRINT @cur
–set @var = @cur
set @int = convert(varchar(20),getdate(),112)
set @rand = substring(convert(varchar(10),rand()*1000,112),0,4)
set @path = ‘D:\POWERSHELL\TEST\New Folder\’+@cur+’_’+@int+’_’+@rand+’.BAK’
–print @path
backup database @cur to disk = @path
FETCH NEXT
FROM curr INTO @cur
END
CLOSE curr
DEALLOCATE curr
GO
Above script is send all full backups to path –> D:\POWERSHELL\TEST\New Folder\ (choose any path .. that works for you !!!)
once we have backups in place .. (or we know which Backups are to deleted according to their age ) .. start you powershell ..
START –> RUN –> POWERSHELL.EXE (will work on all machines above windows XP,Server 2003 ) .. for this 2 .. you need to download and install
you do not have to change you path .. to the folder .. from where you want to delete files .. (example in case of external storage location which is accessed like \\DATADOMAINSERVER01\SQLBACKUP\DAILY_Backups\) .. you can run the powershell script .. from wherever your current path ..
in my case im switching to the location which holds the path .. because i need to show few options ..
the -Recurse option present in DIR or get-CHILDITEM is able to traverse through all sub folders under the parent folder ..
hence with this option you will be able to delete old files in a external storage path like —
\\DATADOMAINSERVER01\SQLBACKUP\DAILY_Backups\
below results show .. that DIR – Recurse is able to trverse through D:\POWERSHELL\TEST\ and a subfolder named New folder
(we excluded the BAK files .. so as to minimize the outcome)
below command will give you backups created 3 hours back .. you will be looking for much older backups and use below command
which works with days instead of hours (i have used below .. as a DEMO )
USE addhours(-30) (to get a count of files and folders which creation date older then 30 days )
get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse | Where-Object {$_.creationdate-lt ($(getdate).adddays(-30))} | measure
NOW : we only want to work with .BAK files and possibly may like to exlcude some files from deletion command … so we will use -include *.BAK (or *.SAFE) and -Exlcude (to exclude any type of files example *.PS1 )
if you use SQL SAFe for backups or any other Backup utility use the required extention
use below command
basically .. we are using -lt (less then comparision) to filter out files older then 30 days .. (in example its older then 2 hours)
below command will give .. only 2 files .. of type *.BAK ..
get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse -include *.BAK -exclu
de *.PS1 | Where-Object {$_.creationtime -lt ($(get-date).adddays(-30))} | select -First 2
use below command to get a exact count of *.BAK files .. you will be deleting
get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse -include *.BAK
-exclude *.PS1 | Where-Object {$_.creationtime -lt ($(get-date).adddays(-30))} | measure
if you trying to get a count of files older then 30 days .. on large file server .. above command will take some time to complete ..
———-command to delete files older then certains Days ————————
********** take care of below points **************
1> mention correct path from where you want to delete backup files (even sub-folder will be taken into account)
2> mention -include *.BAK .. to make sure only backup files are deleted .. also if needed exclude any application or config file
if its possibly on the mentioned path ..
3> make sure to mention correct AGE or no. of days you want to delete files adddays(-30)) .. means you want to delete a month old files or 2 months old files ETC
****************************************************************
get-childitem “D:\POWERSHELL\TEST\New Folder” -Recurse -include *.BAK -exclude *.PS1 | Where-Object {$_.creationtime -lt ($(get-date).adddays(-30))} | remove-item -force
****************************************************************
Above command will delete the backup files of type (*.BAK) and exclude the files of type (*.PS1) older then 30 days
under path D:\POWERSHELL\TEST\New Folder and any sub folder .. it may have ..
try out this command .. and let me know your thoughts ..
ENJOY 🙂
Msg 3140, Level 16, State 5, Line 1 Could not adjust the space allocation for file ‘DBfilename’
We had database which had few tables with image/text (LOB) datatypes .. and after some archeiving of older data … we wanted to reclaim the free space in the database ..
normal Shrink command was not able to reclaim any of free space in the database .. so i decided to go with notruncate and truncate_only options .. but the second part was failing with an error .. Could not adjust the space allocation for file
SCRIPT
go
print ‘start time’
print getdate()
go
DBCC shrinkfile (1,notruncate)
go
print ‘completed first step’
print getdate()
go
DBCC shrinkfile (1,truncateonly)
print ‘completed 2nd step’
print getdate()
out put ::
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
completed first step
Oct 1 2013 2:17AM
Msg 3140, Level 16, State 5, Line 1
Could not adjust the space allocation for file ‘DBfilename’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
completed 2nd step
Oct 1 2013 2:17AM
Solution :
after aboveerror i tried to shrink the DB with DBCC Shrinkfile (1,targetSize) (which had initially failed to shrink any free space)
but this time i was able to shrink the DB to required size … so solution to error such as this is
just Shrink the DB with t-sql script DBCC Shrinkfile (1,targetSize) or use GUI to shrink the DB …
it should complete real fast as all the shrinking has been already done by DBCC shrinkfile (1,truncateonly) …
ue below script to confirm the reduction is the overall DB files ..
select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,’SpaceUsed’)/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a
refer below link for internals of shrink operation
ENJOY 🙂
What’s my OS bit LEVEL 32 or 64
recently I needed to check OS level of few servers most servers in my environment , I was looking for query I could run from
CMS .. to get a consolidated 
data from all servers .
I got an initial idea from link .. A big Thanks to Thomas LaRock
I had few servers where DMV or 2005 + onwards features or tables where
not available .. so I have modified Thomas’s script a bit to suit my needs
below script can be RAN on CMS or individual SQL server
output will have Servername, OS bit level,SQL bit level and whether Server
is VM (works for Hypervisor or VM) or Physical
SELECT
@@servername ‘Servername’,
case
when RIGHT(SUBSTRING(@@VERSION,CHARINDEX(‘<‘,@@VERSION), 4), 2)like ’64’ then ’64’
else ’32’
END ‘OS TYPE’,
case
when RIGHT(SUBSTRING(CONVERT(varchar,SERVERPROPERTY(‘Edition’)),CHARINDEX(‘(‘,CONVERT(varchar,SERVERPROPERTY(‘Edition’))), 3), 2)like’64’ then’64’
else ’32’
end ‘SQL TYPE’,
case when substring(@@version,CHARINDEX(‘Hypervisor’,@@version),10)like’Hypervisor’ then ‘VM’
when substring(@@version,CHARINDEX(‘VM’,@@version),2)like’VM’ then ‘VM’
else ‘physical’ END ‘SERVER TYPE’
let me know if you are facing any issue with this script ..
enjoy 🙂
set SQL error log retention period through T-SQL
we all may need to tweak the SQL error log retention period on a single instance or list of SQL server VIA CMS (central management server)
below 2 commands are quite useful for achieving the same
READ the current setting of sql error log retention
/* USE [master] GO EXEC xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’ GO */
you may receive below error on few server .. you may want to go ahead with the update command and come back to see the results of xp_instance_regread
possible error message for some server ..
RegQueryValueEx() returned error 2, ‘The system cannot find the file specified.’ servername(username): Msg 22001, Level 1, State 1
set the LOG retention sql error to 20 days
/* USE [master] GO EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’, REG_DWORD, 20 GO */
above command will set the retention to 20 days and you may rerun EXEC xp_instance_regread to confirm the same setting
how to add yourself to sysadmin role
one of the best thing about attending SQL Saturday’s is the people you meet and tricks you learn .. (hmmm ok .. even the food included)
i come across similar webcast were i learned below 2 methods to add yourself as sysadmin on any SQL instance ..
example : if the SQL installation was done by someone else and OS admins were never given sysadmins priviliges on SQL instance
so after you get yourself added to OS admin .. you can follow any of the below methods to achieve the same …
NOTE: both methods cause SQL restart .. so take a note of the same …
SQL Saturday 119 Lunch 017 (Photo credit: Michael Kappel)
International Bibliography of Periodical Literature (Photo credit: Wikipedia)
Option 1: http://archive.msdn.microsoft.com/addselftosqlsysadmin/ (the logged in user gets added in sysadmin group)
download the cmd file from above link and save it onto the server .. where you want to add yourself to sysadmin ..
double click the cmd file and it will ask for instance name .. default is taken as the SQL express edition instance …
only enter the instance name (example for instance node-1\SQL2012_INST …. we will just enter SQL2012_INST .. not the network name)
below messages will appear in the cmd window .. (Note : it will cause SQL to restart as it puts instance in single-user mode and restarts it twice )
Adding ‘NODE-1\test’ to the ‘sysadmin’ role on SQL Server instance ‘SQL2012_INST
1’.
Verify the ‘MSSQL$SQL2012_INST1’ service exists …
Stop the ‘MSSQL$SQL2012_INST1’ service …
The SQL Server (SQL2012_INST1) service is stopping.
The SQL Server (SQL2012_INST1) service was stopped successfully.
Start the ‘MSSQL$SQL2012_INST1’ service in maintenance mode …
Add ‘NODE-1\test’ to the ‘sysadmin’ role …
Stop the ‘MSSQL$SQL2012_INST1’ service …
The SQL Server (SQL2012_INST1) service is stopping…
The SQL Server (SQL2012_INST1) service was stopped successfully.
The SQL Server (SQL2012_INST1) service is starting.
The SQL Server (SQL2012_INST1) service was started successfully.
‘NODE-1\test’ was successfully added to the ‘sysadmin’ role.
Press any key to continue . . .
(you can explicitly choose which user/group is to be given sysadmin role, after you get yourself added in OS admin group)
Above is a manual method .. which has the same steps .. ie. Putting instance in single user and then adding a group or user to sysadmin role ..
enjoy 🙂
get running job name from sysprocesses
English: Labeled (SQL) database icon with shortcut identification (Photo credit: Wikipedia)
Spid-1 (Photo credit: Eexlebots)
sometimes we need to find out the job details if multiple jobs are running at once and are involved in blocking or any other server performance issues …
the master..sysprocesses have column named program_name that represents the program from where a particular SPID is originating ..
for running SQL jobs this will be a hexadecimal string .. we can use below method to get the actual name of the job from sysjobs table
1> get the substring of the running job from sysprocess .. have filtered out generic refresher ETC
select substring(program_name,55,7) ‘to search’
from master..sysprocesses
where program_name like ‘%agent%’ and program_name not like ‘%DatabaseMail – SQLAGENT -%’
and program_name not in (‘SQLAgent – Alert Engine’,’SQLAgent – Generic Refresher’,’SQLAgent – Email Logger’,’SQLAgent – Job invocation engine’,’SQLAgent – Job Manager’)
output will be similar to below
Output :
to search
2FE3386
A68D437
3253286
Get the job details from MSDB database by using the results you got from above query and replace them in where clause
1>
select *
from msdb..sysjobs
where job_id like ‘%A68D437%’
OR
2> if you have more than one job running replace below job_id %strings%
select *
from msdb..sysjobs
where job_id like ‘%A68D437%’ or job_id like ‘%4E742EB%’ or job_id like ‘%FAEDF18%’
Enjoy 🙂
SQL DBA learning curve 