Schlagwort: Datenbank

Veröffentlicht am

Datenbank shrinken, aber die Dateien werden nicht kleiner?

Prinzipiell sollte es möglich sein eine Datenbank oder einzelne Dateien, z.B. das LOG zu verkleinern. Aber diese werden niemals kleiner, als die „Inital Size“.

Wenn es jedoch immer noch nicht funktioniert, kann es an vorhandenen Indizes liegen, die auch sehr stark fragmentiert sein können.

Gute „vorbereitete Lösung“ habe ich gefunden bei:

https://blog.pmd-media.com/2009/10/08/howto-verkleinern-einer-microsoft-sql-server-datenbank/

--Erst alle Indizes neu aufbauen
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
   SELECT DISTINCT QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(table_name)
   FROM information_schema.tables
   WHERE table_type = 'base table'
   order by 1 asc
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
   print @TableName+'....'
   DBCC DBREINDEX(@TableName,' ',90)
   FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
print 'FERTIG'

--Und nachher wirklich shrinken:
go
dbcc shrinkdatabase    (0, 10, NOTRUNCATE)
--Erster Parameter := Aktuelle DB
--Zweiter Parameter := Wieviel Prozent soll "leer" bleiben
go
dbcc shrinkdatabase    (0, 10, TRUNCATEONLY)
--dto.
GO

/*
--Quelle: https://qastack.com.de/server/31554/how-to-check-progress-of-dbcc-shrinkfile
--In einem anderem Fenster könnte man sehen wie weit der Shrink-Prozess ist:
select  T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
        , R.cpu_time, R.total_elapsed_time, R.percent_complete as [ca.percent]
from    sys.dm_exec_requests R
        cross apply sys.dm_exec_sql_text(R.sql_handle) T
*/
Veröffentlicht am

MS SQL: Wie lange dauert es noch bis die Datenbank wiederhergestellt ist?

Falls die Wiederherstellung einer Datenbank immer noch nicht fertig ist, wäre es schön, wenn man einmal feststellen kann, wie weit die Wiederherstellung zumindest ist…

**** BITTE CODE VORHER LESEN!!!! **** 

/*
http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/

ACHTUNG: CODE UNTERSCHIEDLICH VOR SQL SERVER 2012: 
	sys.xp_readerrorlog
	sys.sp_readerrorlog

*/


DECLARE @Serverversion integer = 2012  --BITTE CODE VORHER LESEN!!!!
DECLARE @DBName VARCHAR(64) = 'databasename'



If @Serverversion > 2012
BEGIN
	/*
	----------------------------
	SQL Server 2008
	----------------------------
	So, your MSSQL service crashed in the middle of a big transaction? Or you bumped the service while it was rolling back some gigantic schema change (like say a column add on a 800 million row table)? Well, as you prepare your resume in preparation for the fallout from this debockle, you can use the following query to see how much time you have left. Or, I should say, how much time it thinks you have left... which seems to swing wildly up and down... microsoft math ftw.
	*/

	DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
	INSERT INTO @ErrorLog
	EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName
 
	SELECT TOP 5
		 [LogDate]
		,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
		,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
		,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
		,[TEXT]
	FROM @ErrorLog 
	ORDER BY 1 DESC
END
Else
BEGIN
	/*
	----------------------------
	Sql Server 2012 
	----------------------------
	uses a different stored procedure to read the error log:
	*/

	DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 	INSERT INTO @ErrorLog
	EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName
 
	SELECT TOP 5
		 [LogDate]
		,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
		,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
		,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
		,[TEXT]
	FROM @ErrorLog 
	ORDER BY 1 DESC, 2 DESC, 3 DESC
END


Quelle: http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/