Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Wednesday, 13 October 2010

Shrink of SQL log files

Microsoft SQL 2008 can sometimes be tricky to shrink the log files in a maintenance plan. This will force the shrink of the log files:

sp_MSForEachDb 'IF ''?'' NOT IN (''master'', ''tempdb'', ''tempdev'', ''model'', ''msdb'')
AND (SELECT recovery_model FROM master.sys.databases WHERE name = '
'?'') = 1
AND (SELECT is_read_only FROM master.sys.databases WHERE name = '
'?'') = 0
BEGIN
declare @LogFile nvarchar(2000)
USE [?]
SELECT @LogFile = sys.database_files.name
FROM sys.database_files
WHERE (sys.database_files.type = 1)
PRINT @LogFile
EXEC('
'ALTER DATABASE [?] SET RECOVERY SIMPLE'')
DBCC SHRINKFILE (@LogFile, 1)
EXEC('
'ALTER DATABASE [?] SET RECOVERY FULL'')
END'

Wednesday, 11 August 2010

Check services running under user accounts

How to check if service is running under a different user account on all computers?

Using Lansweeper, I only had to make this report:
SELECT     TOP (1000000) tblcomputers.Computername, tblcomputers.ComputerUnique, tblcomputers.Domain, tblServices.Caption AS ServiceDescription, 
tblServices.Lastchanged, tblServices.Startname
FROM tblServices INNER JOIN
tblcomputers ON tblServices.Computername = tblcomputers.Computername INNER JOIN
tblComputersystem ON tblcomputers.Computername = tblComputersystem.Computername
WHERE (tblServices.Startname <> 'LocalSystem') AND (tblServices.Startname <> 'NT AUTHORITY\LocalService') AND
(tblServices.Startname <> 'NT AUTHORITY\NetworkService')
ORDER BY tblcomputers.Computer