To recover the database from suspect mode executes the following query.  EXEC sp_resetstatus ‘yourDBname’; ALTER DATABASE yourDBname SET EMERGENCY DBCC checkdb(’yourDBname’) ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE yourDBname SET MULTI_USER   Then getting      Check terminated. A failure was detected while collecting facts.   Possibly tempdb out of space or a system table is inconsistent. Check   previous errors   How to resolve this?
123114
My database in sql server 2008 has marked suspect.  How can I fix this problem?
80453
During the "install" phase of the upgrade there was a power failure. After when starting up again the following errors are reported:  init: udevtrigger main process (420) terminated with status 1 init: udevtrigger post-stop process (428) terminated with status 1 init: udevmonitor main process (419) killed by TERM signal The disk drive for / is not ready yet or not present Continue to wait; or press S to skip mounting or M for manual recovery   Pressing M gives me the following message:  Root filesystem check failed. A maintenance shell will now be started. CONTROL-D will terminate this shell and reboot the system.
44091
What are /home/user/.cache/dconf and /home/user/.dbus? Why does their backup fail?  I tried following  (see code below) but while it allowed me to backup the above files, now I am told it couldn't backup /home/[user]/.dbus/session-bus.    $ man chown $ sudo chown user .cache/dconf   [sudo] password for user:  $ sudo chown user .dbus $ man chgrp $ sudo chgrp user .cache/dconf $ sudo chgrp user .dbus   How do I fix it so it will not take my time closing an error message after each backup? Should I exclude these folders ? Why are they not needed? Why would someone want to back them up?
37353
We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have been recently updated via SQL Management Studio?  I apologize if this question isn't explaining this very well - I've only been introduced to statistics until now and prior to this would look to indexes whenever I've had performance related issues.  Edit:  I'm using the following but receiving a syntax error:  use *databasename* exec sp_autostats *schema.tablename*   The error I'm receiving is:  Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '.'.   Why is this?
123272
It's fairly straight forward to fix up a single orphaned SQL user to a login using:     EXEC sp_change_users_login 'Auto_Fix', 'user'   I could script this, but is there an existing stored procedure which automatically tries to fix up every orphaned user in a given database?
20510
We're looking to decommission a SQL Server instance which has a couple databases still remaining on it.  How can I tell if they are still being used by users or a web application?  I found a  which had a T-SQL query you could run to retrieve the last query date. It seems to work but I want to know if this information is valid enough to drop databases. Is it?  If you have alternative methods that would help as well.
79655
I have already had the same problem before. On that occasion, I found a program that could scan my entire hard drive for string pieces so I could recover it. Unfortunately I don't remember the name of the program. It had to access the hard drive data directly, normal searches won't get any data from the hard drive before formatting.
35064
I have a database which I tried to defragment all the tables at once by running this T-SQL:  SELECT          'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) +         'ALTER INDEX all ON ' + name + ' REBUILD;'     FROM sys.tables   And then copying and pasting the output to a new query window and running that. I got no errors, but I still have fragmentation. I tried running both commands separately too and still have fragmentation. Note: I have been made aware that REORGANIZE is unnecessary by Aaron, and I'm aware I could use dynamic sql to automate this.  I ran this to determine I still have fragmentation:  SELECT * FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)  WHERE avg_fragmentation_in_percent &gt; 0   And I got:  database_id object_id   index_id    partition_number    index_type_desc alloc_unit_type_desc    index_depth index_level avg_fragmentation_in_percent    fragment_count  avg_fragment_size_in_pages  page_count  avg_page_space_used_in_percent  record_count    ghost_record_count  version_ghost_record_count  min_record_size_in_bytes    max_record_size_in_bytes    avg_record_size_in_bytes    forwarded_record_count  compressed_page_count 85  171147655   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   36.3636363636364    5   2.2 11  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL 85  421576540   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   75  7   1.14285714285714    8   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL 85  965578478   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   14.7058823529412    6   5.66666666666667    34  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL 85  1061578820  1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   40  4   1.25    5   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL 85  1109578991  1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   30.7692307692308    5   2.6 13  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL 85  1205579333  2   1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   50  5   1.6 8   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL 85  1493580359  1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   50  6   1.66666666666667    10  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL   I know I am missing something real basic, but I don't know what.
78306
I have inherited a database server with SQL Server 2005.  The databases have full recovery mode with hourly transaction log backups.  There are two sources taking full backups overnight and hourly transaction log backups: Tivoli Data Protection (TDP) which is part of IBM's Tivoli Storage Manager (TSM) as well as a regular SQL Server Agent job.  If a disaster occurred and I needed to recover the database from backup, would I be missing data?  I was thinking that if the SQL transaction log backup fires off, it will mark the committed transactions in the log file as ready to be overwritten.  After that happens, users will commit more transactions.  When TSM/TDP comes in, it will mark the committed transactions in the log as ready to be overwritten.  If I were to do a restore from either backup (unless it's using only the full backup), then it would not include all of the data.
58737
I Copied some database backups from one server to another server(new instance no databases are present).I tried restoring using below command   Restore database dbname from disk='g:\e2bak\dbname.bak' with  stats=10   It failed with below error     Directory lookup for the file "G:\MSSQL$s73g\MDF\dbname.mdf" failed   with the operating system error 3(The system cannot find the path   specified.)Msg 3156, Level 16, State 3, Line 1   Also server level default database settings are to store data and log in   G:\MSSQL10_50.SG\MSSQL   I can overcome the error by specfying move option. But my confusion here is  1.Why database .bak file is looking for G:\MSSQL$s73g\MDF\dbname.mdf",this is the path   of dbname database files in production from where we copied the .bak file  2.Also when I restored the database with move option and took a backup in same server,   I was able to restore the Database with below command  Restore database dbname  from disk='G:\e1ebk\dbname.bak' with replace,stats=10   Can you please help me understand why .Bak file location is pointing out to some other directory(which doesn't even exist in our server and existed only on production server) and in second case why this worked without move option.
7896
Message that SQL Server Log File Viewer shows:   Login failed for user [User] Error: 18456, Severity: 14, State 38   What it actually means:  Failed to open the explicitly specified database   My Question:  Is there a list somewhere of all the variations of the 18456 errors (Login failed), for each combination of severity and state, with the helpful description text?  I've had a Google but can't find anything other than specific combinations.
70963
Is there a way to prematurely abort a transaction? Say, I have sent a command to the database which runs five minutes and after four, I want to abort it.  Does JDBC define a way to send a "stop whatever you are doing on this connection" signal to the DB?
97905
The database is corrupted and there is no recent backup,  Error from DBCC CHECKDB     Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID   -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:72792) contains an incorrect page ID in its page header. The PageId in the   page header = (0:0). Msg 8998, Level 16, State 2, Line 1 Page errors   on the GAM, SGAM, or PFS pages prevent allocation integrity checks in   database ID 5 pages from (1:72792) to (1:80879). See other errors for   cause.    Any recommendation ? How can i tell which object is corrupted  ?   Errors from DBCC CHECKDB (XXX) with NO_INFOMSGS, all_errormsgs     Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID   -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:72792) contains an incorrect page ID in its page header. The PageId in the   page header = (0:0). Msg 8998, Level 16, State 2, Line 1 Page errors   on the GAM, SGAM, or PFS pages prevent allocation integrity checks in   database ID 5 pages from (1:72792) to (1:80879). See other errors for   cause. CHECKDB found 2 allocation errors and 0 consistency errors not   associated with any single object. CHECKDB found 2 allocation errors   and 0 consistency errors in database 'NSR_M'.
109714
Currently we use standard maintenance plans for backups on the SQL Server 2005/2008/2008R2/2012 servers in our environment, and the "Verify backup integrity" box has always been checked.  Some of the backups run very long, so I've recommended turning off that option, but management needs me to document the impact and risks of this change.  I understand the use and history of this option, it just seems unnecessary to me to double the time for the backup job when (in my opinion), any error that is likely to occur would occur during the backup step, not during the verify.  Am I wrong? Is it minimal risk to turn this off, if I am backing up to disk and not to streaming tape or something? (We back up over the network to an EMC DD-800 backup appliance, if that is relevant.)  Is there any official MS recommendations for when it is safe to turn this off?   Do you run "verify" on every backup in your environment? Do you spot-check them?  EDIT: To clarify, when you check "verify backup integrity" in the maintenance plan, SQL will do a full RESTORE VERIFYONLY on each database immediately after each backup. This is just as data/IO intensive as the original backup, and (basically) doubles the overall time of the backup job. This is not the same as enabling the "checksum" option on backup (which can't be done in the wizard, as far as I know).
80496
Well, luckily I got this error on my test environment. I'm so scared about this error that I disabled all logon triggers I was using on production environment.   I created the famous logon trigger, and I put a database in offline mode just to mess with something else. Then I discover that because the trigger can't find the table, I can't access the instance anymore. There are plenty of questions related to this, but even doing everything I could find, I still can't acces the instance, not even with SQLCMD.  I'm doing this:  sqlcmd -S server\instance -U sa -P &lt;pass&gt;  -A   If I don't use -A I receive the famous error:  logon failed for sa due trigger execution.   If I use -A:     Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server   Network Interfaces: An error occurred while obtaining the dedicated   administrator connection (DAC) port. Make sure that SQL Browser is   running, or check the error log for the port number [xFFFFFFFF]. .   Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout   expired. Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A   network-related or instance-specific error has occurred while   establishing a connection to SQL Server. Server is not found or not   accessible. Check if instance name is correct and if SQL Server is   configured to allow remote connections. For more information see SQL   Server Books Online..   Why i'm not able to connect to the sql server    sql server express here.  I could enable SQL Browser, but I still can't access it.  I don't think this question is duplicated because is not about the trigger, is about Why I can't access the server with SQLCMD. I pretty much know how to fix this. I just don't know how can I acces sql server without triggering the trigger.
90246
My server rebooted and I am not sure why.  Is there a way to find out, e.g. perhaps someone did it manually, or as a result of a windows update or a blue screen?  I've looked in the Event Viewer, but I am not sure what to look for - there are million items there.
2635
I am not able to get the meanings of DB Session  status. I searched it for some references, but not getting any help. Can anyone explain these status (runnable, sleeping, suspended, running and background) what actually means, that would be help me a lot. Thanks in advance.