March 19, 2015 at 3:40 pm
when I run this query: select #tempsysdatabase.name, count (#temploginfo.FieldId) as FieldID
from #temploginfo
inner join #tempsysdatabase
on #tempsysdatabase.File_id = #temploginfo.FieldId
where fieldID not in
(
select (fieldId)
from #temploginfo
where status <> 0
group by fieldID
having count(*) > 0
)
group by name
I get the following resultset:
name FieldID
rdb_log34
rdb_log44
However, my objective is to finally remove both of those log files. How can I do it using the same query?
March 19, 2015 at 4:45 pm
If by "remove log files" you want to delete the records in tempsysdatabase where the names are: rdb_log3 and rdb_log4
This should work:
DELETE FROM #tempsysdatabase
WHERE name IN
(
select #tempsysdatabase.name, count (#temploginfo.FieldId) as FieldID
from #temploginfo
inner join #tempsysdatabase
on #tempsysdatabase.File_id = #temploginfo.FieldId
where fieldID not in
(
select (fieldId)
from #temploginfo
where status <> 0
group by fieldID
having count(*) > 0
)
group by name
) logstodelete;
-- Itzik Ben-Gan 2001
March 19, 2015 at 6:46 pm
Not really Alan, I want to remove the extra log files that the database has. I somehow need to make sure that the secondary log files are not being used before I am able to remove them. So, here's what I have so far:
1. insert result from sys.database_files into a temp table to check the number of log files.
2. If the number of log files is more than 1, then insert the result from dbcc sqlperform(logspace) to another temp table.
2, Check the log space being used. If the particular database is using logspace, then insert the result from dbcc loginfo(dbname) into a temp table.
3. from the 3rd temp table, look for the files which have the status 0 for all of their VLFs.
4. If not all the files have the status 0, then take the database to single user mode and take a log backup. I switch the databse to single user mode just to make sure that the transaction logs stay free until the script executes.
These are the processes I would like to add:
1. I would like the log backup to iterate until all the logs are free.
2. Once the logs are free I would like to get the logical file names for all the secondary log files and delete them .
3. Switch the database back into multi user mode.
Here's what I have done so far. Any help would be nice.. thank you .
create table #tempsysdatabase(
File_id int,
file_guid varchar(50),
type_desc varchar (20),
data_space_id int,
name nvarchar (50),
state int,
state_desc varchar (25),
size int,
max_size int,
growth int
)
insert #tempsysdatabase (File_id, file_guid, type_desc, data_space_id, name, state, state_desc, size, max_size, growth)
exec ('select File_id, file_guid, type_desc, data_space_id, name, state,state_desc, size, max_size, growth from rdb.sys.database_files')
if (select count(*) from #tempsysdatabase where type_desc = 'log') = 1
BEGIN
print N'no need to remove any log file.'
END
ELSE
create table #TmpLOGSPACE(
DatabaseName varchar(100)
, LOGSIZE_MB decimal(18, 9)
, LOGSPACE_USED decimal(18, 9)
, LOGSTATUS decimal(18, 9))
insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS)
EXEC('DBCC SQLPERF(LOGSPACE);')
if (select LOGSPACE_USED from #TmpLOGSPACE where DatabaseName = 'rdb') > 0
BEGIN
create table #temploginfo(
FieldID int NOT NULL,
FileSize int,
StartOffset int,
FSeqNo int,
Status int,
Parity int,
CreateLSN int,
)
insert #temploginfo (FieldID, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)
exec ('DBCC Loginfo(rdb)')
End
IF not Exists (select #tempsysdatabase.name, count (#temploginfo.FieldId) as FieldID
from #temploginfo
inner join #tempsysdatabase
on #tempsysdatabase.File_id = #temploginfo.FieldId
where fieldID not in
(
select (fieldId)
from #temploginfo
where status <> 0
group by fieldID
having count(*) > 0
)
group by name )
Begin
backup log rdb to disk = N'C:\rdb.trn'
End
USE master;
GO
ALTER DATABASE rDb
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
drop table #temploginfo
create table #tmploginfo(
FieldID int NOT NULL,
FileSize int,
StartOffset int,
FSeqNo int,
Status int,
Parity int,
CreateLSN int,
)
insert #tmploginfo (FieldID, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)
exec ('DBCC Loginfo(rdb)')
If exists (select fieldID from #tmploginfo where status <> 0
group by fieldID
having count(*) > 0)
begin
backup log rdb to disk = N'C:\rdb1.trn'
End
March 20, 2015 at 8:47 am
Sorry, I completely misunderstood your requirement. I will look at this again later today.
-- Itzik Ben-Gan 2001
March 21, 2015 at 2:09 pm
thanks Alan, that would be very helpful.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply