Hot answers tagged

12

Here is the answer to my own question. Run the below query to get information about the log file's reuse wait: SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'DBName' I got the following output: log_reuse_wait_desc ------------------- REPLICATION There were some replication-related objects remaining in the database, even after removing the ...


12

If you restore a database as a new database, the dbi_crdate field is the restore date/time. Here's how to reproduce it: Create a new database. Run DBCC CHECKDB() on it. Look at DBCC DBINFO() for the creation date and DBCC dates. Back up the database. Delete the database. Restore the database (if you want to get fancy, use a different database name.) Look ...


11

There is no built-in consistency check command or tool in PostgreSQL. The general view is that one should not be necessary, as corruption and inconsistency should not be possible on a quality hardware/software stack. If problems do arise, there's no guarantee any kind of consistency check would find them, so it'd just create a false sense of security. I don'...


10

Steps for shrinking the log are going to be Backup transaction log through either SSMS or T-SQL and then perform a shrink commands for SSMS are under the tasks if you right click the database name BACKUP LOG <Databasename> TO DISK N'<path\database_log.ldf'; GO DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS You will ...


10

Read How to Shrink SQL Server log for an explanation how the circular nature of the log may prevent shrink after truncation. Is possible that you log's last LSN point into a VLF that is at the tail of the LDF. Counter intuitively you must advance the log, by generating log writes, to allow it to shrink.


10

All things being equal, it should be enough to compact the large object (LOB) column OriginalHTML. You don't specify the clustered index name in the question, so: ALTER INDEX ALL ON dbo.Articles REORGANIZE WITH (LOB_COMPACTION = ON); See ALTER INDEX (Transact-SQL) If you have the clustered index name (not just the clustered column(s)), replace the ALL ...


8

In addition to the great answer by armitage you probably do not need to use DBCC CLEANTABLE in your scenario. You state Then I ran an index reorg on ALL indexes and reclaimed and additional 60GB.. The best practices in the Microsoft documents says: DBCC CLEANTABLE should not be executed as a routine maintenance task. Instead, use DBCC CLEANTABLE ...


7

3 important pieces of information are missing to pinpoint exactly what went wrong in your particular scenario: What where the exact steps you took, from the moment you found a problem until the moment you discovered that replication didn't work? What was broken, what was repaired, what was lost? All of this information is available in the output of CHECKDB. ...


7

The transaction log does not not contain statements, it contains the physical changes occurred in the database. If you see a log record that indicate a delete you cannot know if this was a DELETE statement, a MERGE statement or a wide (split) UPDATE statement. If you see an operation indicating an INSERT you cannot know if it was an INSERT (...) VALUES (...) ...


7

If you are running SQL Server 2008 R2 SP1 and up then you can query sys.dm_server_registry to find out TRACE FLAGS that are enabled at startup. select * from sys.dm_server_registry where cast(value_data as varchar(max)) like '%-T%' Just incase if you or future visitors need the output in one row then, you can use below T-SQL : select distinct @@...


6

As you have an old backup with the correct schema, the problem database is online and you've successfully queried several tables, I'd be inclined to try get a dump of the raw data as fast as possible. Shut down the applications accessing the database. BCP export the data, table by table to files. As disk corruption could be the source of your woes, export ...


6

can I terminate it without causing any problem on the DB You already caused the problem, simply by running repair_allow_data_loss. Allow data loss is really the very very very last resort, after all other options have been exhausted. The appropriate action is to restore from a correct backup and re-apply missed transactions. Can DBCC take 27 hours? Yes, I ...


6

Have you tried what Paul Randal recommends? Creating, detaching, re-attaching, and fixing a suspect database Create a new dummy database with the exact same file layout and as close as possible to the file sizes of the detached database Shutdown SQL Server Swap the corrupt database files Re-start SQL Server Use emergency-mode repair


6

There is a project called pgCheck on pgFoundry. Note that the development status is 'Alpha' however. I looks like the last activity was in early 2012. It's been suggested elsewhere that most people use a combination of either database wide vacuums, or doing select * from each table; ie. try to scan / process every row somehow


6

My goal is to execute a command that requires the sysadmin role (DBCC TRACEON(1224)) You are punching a hole in your security by allowing an unprivileged user run as sysadmin role. If you are trying to set 1224 traceflag, which disables lock escalation based on the number of locks, you can do it on table level using ALTER TABLE e.g. Below enables lock ...


6

I ended up opening an incident with Microsoft and it was determined that the sys.sysrscols (that can only be accessed via DAC) had over 200 million rows. After they did a review it was found as part of DBCC SQL Server always does checks on system tables even for DBCC CHECKTABLE. From the DBCC CHECKTABLE manual: Integrity checks are always performed on ...


6

How can we access that algorithm directly? In so far as the answer to this question, specifically, you can't access it directly. There is nothing where you can say SELECT GetMeMaxRowSize(MyTable, MyPartition, MyIndex). However, as LowlyDBA has pointed out, you can use sys.dm_db_index_physical_stats to give you more information than ShowContig. The quite ...


5

It can be done but it's generally considered fairly dangerous. At a very basic level you set the trustworthy flag on the database and then when use you execute as on the sp it can take advantage of it's server level principals security access. Because of how dangerous this is I don't want to go into any detail here. However I've blogged about it here ...


5

The trace flags that you have turned on will tell you what a checkpoint is doing behind the scenes. 3502: writes to the error log when a checkpoint starts and finishes 3504: writes to the error log information about what is written to disk 3605: allows trace prints to go to the error log Refer to Paul Randall's blog post for more details on the above. Also,...


5

I ran into a similar issue. However, I was unable to get the database out of suspect as the backups were suspect as well. I used the exporting functionality in SSMS to move the tables and data to new blank database. This however does not move all of your stored procedures, views, functions, etc. I have a copy of RedGate's SQL Compare (which I highly ...


5

First, I'd step back and ask what measurements you plan to collect during the test. If you're counting logical reads by query, for example, then you don't need to free the cache. I'm a big fan of using logical reads because it's independent of whether the data is cached or on disk - and in production, it's hard to guess whether a query's data will be ...


5

Restarting the server should be enough - those worktables should clear out. But I'd probably start it up in single user mode (-m) to prevent other processes from creating worktables before you successfully remove those files. Then redefine the files required for tempdb; perhaps deleting unnecessary files, changing sizes, etc. You should also ensure you have ...


5

The reason the server wasn’t sending emails is due to this flag not being set: EXEC master..sp_altermessage 1205, 'WITH_LOG', TRUE; GO Even though it was logging in the event log via the trace flag, this also needs to set in order to trigger the emails. You can see the table here: select * from master.sys.messages where text like '%deadlock%'


5

I was able to reproduce your issue and this might be one of the reason for no result. Cause: When you create a table and when no record is present in the table and you run dbcc ind on such table then you get the "message like below but NO tabular result". DBCC execution completed. If DBCC printed error messages, contact your system administrator. If ...


5

According the the information in this MSDN forums answer by Sankar Reddy (and other posts when searching for DBCC loginfo status 2): Status = 2 means that VLF can't be reused (overwritten) at this time and it doesn't necessarily mean that VLF is still active and writing transactions to that VLF. It means that the VLF is waiting for backup/REPL/...


5

I poked around in the file header page, as suggested by Martin Smith in the comments. I think this is part of the answer, but it's mostly speculation based on observing changes to the file header page flag values between performing shrinks and other operations. First I created a database to test with, including a secondary filegroup: CREATE DATABASE [...


5

The error message you're seeing: Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C. Occurs when sqlcmd is asked to quit while it is waiting for a response from SQL Server. Most likely someone (or something) killed the spawned cmd.exe process that was running SQLCMD.exe, which resulting in that error message being ...


4

Do not take the database offline. Also do not shut down the server. Either might make the database totally inaccessible. Take a full "copy only" backup. Try to read the backup file with Red-Gate datacompare use this query with the allocation_unit_id values you have in the checkdb output: SELECT o.name,p.index_id,p.partition_number FROM sys.allocation_units ...


4

Shrinkfile on a datafile is a single-threaded operation, reusing a small memory buffer. So the Ninja hardware hasn't got an edge with the extra memory and the 80 cores. Your local PC however has the benefit of local I/O latency (local disk, i.e. not having to make multiple trips to the SAN).


4

Instead of continuously shrinking log files changing recovery model to simple, just leave your database in simple recovery model. You do not need the full recovery because instead of taking regular log backups you are continuosly breaking log backup chain (that's if you've EVER taken log backups), so it has no sense at all to have a full recovery model: ...


Only top voted, non community-wiki answers of a minimum length are eligible