Never detach a Suspect database. Anyway, how did you attach the database after detaching it? You used CREATE DATABASE
with FOR ATTACH_REBUILD_LOG
option?
These commands should have done the trick:
ALTER DATABASE recovery_test_2 SET EMERGENCY;
ALTER DATABASE recovery_test_2 SET SINGLE_USER;
DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS, ALL_ERRORMSGS;
I wrote a post for this situation:
SQL 2005/2008 Database Recovery Procedure – Log File Deleted (Part 3)
You asked about the difference between:
DBCC CHECKDB ('<dbname>', REPAIR_ALLOW_DATA_LOSS)
and
ALTER DATABASE <dbname> REBUILD LOG ON (NAME=<dbname>,FILENAME='<logfilepath>')
The thing is that you can run both to rebuild the log file, but with CHECKDB
you rebuild the log and check database for integrity errors as well.
Also the second (alter database) will not work if there were active transactions (not written to disk) when the log file was lost. At start-up or attach, SQL Server will want to perform recovery (rollback and rollforward) from the log file which is not there. It happens when a disk crashes or an unexpected shutdown of the server occurs and the database is not cleanly shutdown. I guess it was not your case and all sorted out good for you.
DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS)
run on a database in emergency status checks the database for inconsistency errors, tries first to use the log file to recover from any inconsistencies. If this is missing, the transaction log is rebuilt.
ALTER DATABASE REBUILD LOG ON...
is an undocumented procedure and requires a subsequent DBCC CHECKDB
to fix any errors.
The only way to find out what process caused the autogrowth is to use Extended events esp. EVENT
--> sqlserver.database_file_size_change
& sqlserver.databases_log_file_size_changed
and ACTION
--> sqlserver.sql_text
.
Looks like @DBA_ANDY already did the hard work of writing an XEvent
-- Original Author : @DBA_ANDY http://nebraskasql.blogspot.com/2016/06/finding-file-growths-with-extended.html
-- Modified by : Kin to filter on tempdb autogrowth events
SET NOCOUNT ON
/* Create Extended Events Session */
IF EXISTS (SELECT 1 FROM master.sys.server_event_sessions WHERE name = 'DemoFileSize')
DROP EVENT SESSION [DemoFileSize] ON SERVER
GO
CREATE EVENT SESSION [DemoFileSize] ON SERVER
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1)
ACTION(package0.collect_system_time,sqlos.task_time,
sqlserver.client_app_name,sqlserver.client_hostname,
sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
sqlserver.server_instance_name,sqlserver.session_id,
sqlserver.sql_text,sqlserver.username)
WHERE ( [database_id] = ( 2 ) -- This is tempdb database id
),
/* Note - predicate/filter - will collect only for tempdb */
ADD EVENT sqlserver.databases_log_file_size_changed(
ACTION(package0.collect_system_time,sqlos.task_time,
sqlserver.client_app_name,sqlserver.client_hostname,
sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
sqlserver.server_instance_name,sqlserver.session_id,
sqlserver.sql_text,sqlserver.username)
WHERE ( [database_id] = ( 2 ) -- This is tempdb database id
)
/* Note - predicate/filter - will collect only for tempdb */
ADD TARGET package0.event_file(SET filename=N'D:\XEvent_logs\DemoFileSize.xel',-- change HERE !!
max_file_size=(500),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [DemoFileSize] ON SERVER
STATE = START;
GO
shred the XEvent xml ...
SELECT
Case when file_type = 'Data file' Then 'Data File Grow' Else File_Type End AS [Event Name]
, database_name AS DatabaseName
, dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), timestamp1) as LocalTimeStamp
/* added the timestamp and in XE is UTC - this code converts it to local server time zone */
, file_names
, size_change_mb
, duration
, client_app_name AS Client_Application
, client_hostname
, session_id AS SessionID
, sql_txt ----------------> This is the process TSQL text !
, sql_username
, Is_Automatic
FROM
(
SELECT
(n.value ('(data[@name="size_change_kb"]/value)[1]', 'int')/1024.0) AS size_change_mb
, n.value('(@timestamp)[1]', 'datetime2') as timestamp1
, n.value ('(data[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name
, n.value ('(data[@name="duration"]/value)[1]', 'int') AS duration
, n.value ('(data[@name="file_type"]/text)[1]','nvarchar(50)') AS file_type
, n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name
, n.value ('(action[@name="session_id"]/value)[1]','nvarchar(50)') AS session_id
, n.value ('(action[@name="client_hostname"]/value)[1]','nvarchar(50)') AS Client_HostName
, n.value ('(data[@name="file_name"]/value)[1]','nvarchar(50)') AS file_names
, n.value ('(data[@name="is_automatic"]/value)[1]','nvarchar(50)') AS Is_Automatic
, n.value ('(action[@name="sql_text"]/value)[1]','nvarchar(500)') AS sql_txt
, n.value ('(action[@name="username"]/value)[1]','nvarchar(50)') AS sql_username
FROM
(
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
N'D:\XEvent_logs\DemoFileSize*.xel' -- CHANGE HERE !!
, NULL
, NULL
, NULL
)
) AS Event_Data_Table
CROSS APPLY event_data.nodes('event') AS q(n)) xyz
ORDER BY timestamp1 desc
Below is the output:
Important things :
Best Answer
Here's a guess:
When you seed (auto or through a backup) the database, the info from sys.database_files is copied into sys.master_files on the other end (which lives in a table in the master database).
My guess is that a later change for the file properties, which is stored in (what you see though) sys.database_files is not reflected in sys.master_files. Perhaps some changes triggers the state to be copied into sys.master_files and some change don't? And perhaps this has been improved in later versions?
Sorry to be so vague, I don't have the environment here to test this. I would do some changes to the ldf file and see if any of those changes causes sys.master_files at the other end to be updated.
Increase the file size. Shrink it (just 1 MB). Change max size. Etc. One at a time, of course.