The log file is too big? This is the solution
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER Procedure [dbo].[sp_EasyDetAtt] @MinSize int /* = 100 --(MB)*/,@RealDettach char(1)
AS
BEGIN
-- DataFiles List --
SET NOCOUNT ON
if exists (SELECT [id]
FROM tempdb..sysobjects
WHERE [id] = OBJECT_ID ('tempdb..#TempForFileStats '))
DROP TABLE #TempForFileStats
DECLARE @DBName nvarchar(40)
DECLARE @SQLString nvarchar (2000)
DECLARE c_db CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE status&512 = 0
AND dbid >4
-- Create Temp Hold Result Tables --
CREATE TABLE #TempForFileStats([Server Name] nvarchar(40),
[Database Name] nvarchar(20),
[File Name] nvarchar(128),
[Usage Type] varchar (6),
[Size (MB)] real,
[Space Used (MB)] real,
[MaxSize (MB)] real,
[Next Allocation (MB)] real,
[Growth Type] varchar (12),
[File Id] smallint,
[Group Id] smallint,
[Physical File] nvarchar (260),
[Date Checked] datetime)
CREATE TABLE #TempForDataFile ([File Id] smallint,
[Group Id] smallint,
[Total Extents] int,
[Used Extents] int,
[File Name] nvarchar(128),
[Physical File] nvarchar(260))
CREATE TABLE #TempForLogFile ([File Id] int,
[Size (Bytes)] real,
[Start Offset] varchar(30),
[FSeqNo] int,
[Status] int,
[Parity] smallint,
[CreateTime] varchar(20))
DECLARE @tblConnectedUsers TABLE (SPID int)
DECLARE @KillExp VARCHAR(200),@iSPID int
OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'SELECT @@SERVERNAME as ''ServerName'', ' +
'''' + @DBName + '''' + ' as ''Database'', ' +
' f.name, ' +
' CASE ' +
' WHEN (64 & f.status) = 64 THEN ''Log'' ' +
' ELSE ''Data'' ' +
' END as ''Usage Type'', ' +
' f.size*8/1024.00 as ''Size (MB)'', ' +
' NULL as ''Space Used (MB)'', ' +
' CASE f.maxsize ' +
' WHEN -1 THEN -1 ' +
' WHEN 0 THEN f.size*8/1024.00 ' +
' ELSE f.maxsize*8/1024.00 ' +
' END as ''Max Size (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8/1024.00) ' +
' WHEN f.growth =0 THEN 0 ' +
' ELSE f.growth*8/1024.00 ' +
' END as ''Next Allocation (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' ' +
' ELSE ''Pages'' ' +
' END as ''Usage Type'', ' +
' f.fileid, ' +
' f.groupid, ' +
' filename, ' +
' getdate() ' +
' FROM [' + @DBName + '].[dbo].[sysfiles] f'
INSERT #TempForFileStats
EXECUTE(@SQLString)
------------------------------------------------------------------------
SET @SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS'
INSERT #TempForDataFile
EXECUTE(@SQLString)
--
UPDATE #TempForFileStats
SET [Space Used (MB)] = s.[Used Extents]*64/1024.00
FROM #TempForFileStats f,
#TempForDataFile s
WHERE f.[File Id] = s.[File Id]
AND f.[Group Id] = s.[Group Id]
AND f.[Database Name] = @DBName
--
TRUNCATE TABLE #TempForDataFile
-------------------------------------------------------------------------
SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO'
INSERT #TempForLogFile
EXECUTE(@SQLString)
--
UPDATE #TempForFileStats
SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) +
SUM(CASE
WHEN l.Status <> 0 THEN l.[Size (Bytes)]
ELSE 0
END))/1048576.00
FROM #TempForLogFile l
WHERE l.[File Id] = f.[File Id])
FROM #TempForFileStats f
WHERE f.[Database Name] = @DBName
AND f.[Usage Type] = 'Log'
--
TRUNCATE TABLE #TempForLogFile
-------------------------------------------------------------------------
FETCH NEXT FROM c_db INTO @DBName
END
CLOSE c_db
DEALLOCATE c_db
SET @DbName = ''
--- List Of Databases for detach/attaching
SELECT * FROM #TempForFileStats
SELECT x.[Database Name] as [Database Name],
x.[Physical File] as ldf,
y.[Physical File] as mdf,
x.[File Name] as logname
FROM #TempForFileStats x
JOIN #TempForFileStats y
ON (x.[Database Name] = y.[Database Name]
AND y.[Usage Type] = 'Data'
AND y.[File Id] = 1)
WHERE x.[Usage Type] = 'Log'
AND x.[File Id] = 2
AND x.[Size (MB)] >= @MinSize
---
DECLARE @DbPhysNameOld SYSNAME
DECLARE @DbPhysNameNew SYSNAME
DECLARE @DbMdfFileName SYSNAME
DECLARE @LogName SYSNAME
DECLARE @ShellCMD NVARCHAR(2000)
DECLARE @ShellForDelete NVARCHAR(2000)
DECLARE n_db CURSOR FOR SELECT x.[Database Name],x.[Physical File] as ldf,y.[Physical File] as mdf,x.[File Name] as logname
FROM #TempForFileStats x
JOIN #TempForFileStats y
ON (x.[Database Name] = y.[Database Name]
AND y.[Usage Type] = 'Data'
AND y.[File Id] = 1)
WHERE x.[Usage Type] = 'Log'
AND x.[File Id] = 2
AND x.[Size (MB)] >= @MinSize
OPEN n_db
FETCH NEXT FROM n_db INTO @DbName,@DbPhysNameOld,@DbMdfFileName,@LogName
WHILE @@FETCH_STATUS = 0
BEGIN
print ltrim(rtrim(@DbName))+' '+ltrim(rtrim(@DbMdfFileName))
INSERT INTO @tblConnectedUsers
SELECT p.spid
FROM master.dbo.sysprocesses p WITH (NOLOCK)
join master.dbo.sysdatabases d WITH (NOLOCK)
ON p.dbid = d.dbid
WHERE d.[name] = @DbName
IF @@ROWCOUNT > 0
BEGIN
WHILE 1 = 1
BEGIN
SELECT TOP 1 @iSPID = SPID
FROM @tblConnectedUsers
WHERE SPID > ISNULL(@iSPID, 0)
ORDER BY SPID ASC
-- Break if no more users
IF @@ROWCOUNT = 0
BREAK
SET @KillExp = 'Kill ' + CONVERT(VARCHAR(10), @iSPID)
EXEC( @KillExp )
END
DELETE @tblConnectedUsers
END
-- Detach database
if UPPER(@RealDettach)='Y'
BEGIN
EXEC master..sp_detach_db @Dbname,TRUE
WAITFOR DELAY '00:00:05'
EXEC xp_cmdshell 'cd c:\temp',NO_OUTPUT --check if c:\temp exists
IF @@error = 1
EXEC xp_cmdshell 'mkdir c:\temp',NO_OUTPUT --create c:\temp if not exists
-- Move (OS) The old log file to c:\temp
IF CHARINDEX(' ',@DbPhysNameOld)>0
SET @ShellCMD = 'MOVE "'+LTRIM(RTRIM(@DbPhysNameOld))+'" '+'c:\temp'
ELSE
SET @ShellCMD = 'MOVE '+LTRIM(RTRIM(@DbPhysNameOld))+' '+'c:\temp'
PRINT LTRIM(RTRIM(@ShellCMD))
EXEC master..xp_cmdshell @ShellCMD,NO_OUTPUT
WAITFOR DELAY '00:00:05'
-- Attach the single mdf file
EXEC master..sp_attach_single_file_db @Dbname,@DbMdfFileName
IF @@ERROR = 0
BEGIN
PRINT '****************************************************'
PRINT @Dbname+' attaching success'
PRINT '****************************************************'
END
ELSE
BEGIN
PRINT '***************************************************'
PRINT 'Check '+@Dbname+' and attach him manualy !!!'
PRINT '***************************************************'
END
END
SET @ShellCMD = ''
SET @DbPhysNameNew = ''
FETCH NEXT FROM n_db INTO @DbName,@DbPhysNameOld,@DbMdfFileName,@LogName
END
CLOSE n_db
DEALLOCATE n_db
DROP TABLE #TempForDataFile
DROP TABLE #TempForLogFile
DROP TABLE #TempForFileStats
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.
2003-01-13
18,069 reads
What's this error:Arithmetic overflow error converting IDENTITY to data type int? It was a new one to me, but read on to find out what it means.
2002-10-14
6,900 reads
This article shows you how to design the storage for email addresses, how to validate email addresses, how to retrieve demographic information from email addresses efficiently, using computed columns and indexes. It also covers the security aspect of dealing with email addresses.
2002-08-01
1,340 reads
Binary data can be stored as integers in a table. This article explains how to query an integer field to return the bits represented by the integer.
2002-06-28
4,567 reads
This article demonstrates how to store checkbox results as integers in a database...perfect for surveys!
2002-06-27
4,427 reads