IT ›  Hlava děravá ... › 

SQL poznámky... IV.

Buck the Bug
Nějak se mi to nahromadilo, takže opravdu poznámky pro mě samotného. Možná časem něco rozvedu do srozumitelné podoby, ale nic neslibuji...

1) Velikost transakčního logu
http://weblognicek.chvojka.cz/programovani/ostatni/zmenseni-transakcniho-logu-na-mssql-290.aspx
http://www.ajtik.cz/Default.aspx?tabid=152&EntryID=18

2)  How to configure an instance of SQL Server to listen on a specific TCP port or a dynamic port:
http://support.microsoft.com/?id=823938

3) Neověřené možnosti při problémech instalace SQL05EE na XP SP3
http://support.microsoft.com/kb/222473
A poznámka bez kontextů... ;)
"%SystemRoot%\System32\REG.exe" ADD "\\%ComputerName%\HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon" /v SFCDisable /t REG_DWORD /d 2 /f

4) Seznam fixů po SP2 pro SQL2005
http://support.microsoft.com/kb/937137/LN/
a Buildy pro verze od 7.0 výše
http://sqlserverbuilds.blogspot.com/

5) Free Profiler nejen pro Express Edition
http://sqlprofiler.googlepages.com/
Měl by fungovat i bez instalace. Svého času jsem jej nosil na flashdisku...

8) Velká kapitola, která by si asi opravdu zasloužila více místa... Emergency mode a "zeblité databáze"
http://www.sqlskills.com/...COVERYPENDINGOrSUSPECTDatabase.aspx
http://www.sqlskills.com/...RepairTheVeryVeryLastResort.aspx

Pro pořádek ještě stará dobrá sp_attach_single_file_db, místo které by se do budoucna mělo používat CREATE DATABASE database_name FOR ATTACH

A výpisky z různých fór, opět bez kontextu ...
/*
How to restore a SQL Server database marked as "suspect"
At times a database appears to be marked as "suspect" in the Enterprise Manager.
SQL Server marks a database as suspect with it can't access the database.
What happens at a low level is that SQL Server sets one of the bits in the status field in the sysdatabases table.
*/
--1/ Nutno povolit ad hoc queries.
--SQL2000
SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
--SQL2005
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE
/*
In general, this problem has no trivial solution.
A simpler case occurs when the file that hosts the database is renamed from the command prompt or Windows Explorer.
In this case, you just have to restore the original file name and then manually restore the bit in the status field with this command.
Keep in mind that the command can be successful only after enabling writing to system tables.
*/
--2/
update sysdatabases
set status = status & ~256
where name = 'MySuspectDatabase'
/*
When the error isn't so trivial, you should check the error log to determine whether a restore operation is possible.
However, when the data in the transaction log has been damaged, the restore procedure can hardly succeed.
In this case the best solution is to restore the database from a recent backup.
When this isn't possible, you should bring SQL Server in the so-called emergency mode.
This state is entered by setting a bit in the status field in the sysdatabases table.
The docs in the Books On Line report that you must OR the field contents with the value 32768, but the correct value is -32768.
Thus, this is the command you need:
*/
--3/
update sysdatabases
set status = status | -32768
where name = 'MySuspectDatabase'
/*
After this command you must stop and restart the SQL Server service.
The Emergency Mode prevents SQL Server from restoring the suspect database, which now appears to be available.
If the database is now accessible, you should be able to read data using standard techniques, such as a bulkcopy command or SELECT INTO commands.
*/
--reset Status normalni DB (autoshrink status)
update sysdatabases
set status = status | 4194304
where name = 'MySuspectDatabase'


 IT  Hlava děravá ...  0 Komentáře Buck the Bug, December 15th, 2008



Aktuální články

Aktuální komentáře

Kategorie

Linky

RSS blogu

Podporuji

Behej.com
Running Training Log
Opera, the fastest and most secure web browser Connectria.com

Reklama


Evidence