Help with a Query

Here is some drastically stripped down DDL for a Help Desk system I
wrote. I only left the relevant columns, and didn't script any of the
relationships, etc.
CREATE TABLE [HelpDesk_Issue] ([Id] [int])
GO
INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
GO
CREATE TABLE [HelpDesk_IssueHistory] (
[Id] [int],
[IssueId] [int],
[UserIdEnteredBy] [int],
[DateEntered] [datetime]
GO
INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
[DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
GO
INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
[DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
GO
INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
[DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
GO
HelpDesk_Issue is a table containing Help Desk issue entries, and
HelpDesk_IssueHistory is a table containing modification history
records for the Help Desk issues.
I want to write a query to retrieve values for LastUpdated, and
LastUpdatedBy.
LastUpdated is pretty easy. I might simply be brainfarting on not
knowing how to do a HAVING properly, but the only way I can retrieve
LastUpdatedBy is:
SELECT
LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
Where IssueId = I.[Id]),
LastUpdatedBy =
(
Select
UserIdEnteredBy
From
HelpDesk_IssueHistory
Where
Id =
(
Select
MAX(H.Id)
From
HelpDesk_IssueHistory H
Where
IssueId = I.[Id]
)
)
FROM
HelpDesk_Issue I
My result set should be:
Date Entered UserIdEnteredBy
2004-11-05 15:25:18.120 3
This query works, but is unacceptably slow, and there's got to be a
cleaner way of doing it.
Thank you!Hi
Maybe something like:
SELECT I.id, I.DateEntered, I.UserIdEnteredBy AS LastUpdatedBy
FROM HelpDesk_Issue I
JOIN (Select Id, MAX(DateEntered) AS LatestDateEntered From
HelpDesk_IssueHistory GROUP BY Id ) L ON I.id = L.id and I.DateEntered =
L.LatestDateEntered
Assuming that DateEntered is unique!
John
<george.durzi@.gmail.com> wrote in message
news:1122230790.079548.152910@.g44g2000cwa.googlegroups.com...
> Here is some drastically stripped down DDL for a Help Desk system I
> wrote. I only left the relevant columns, and didn't script any of the
> relationships, etc.
> CREATE TABLE [HelpDesk_Issue] ([Id] [int])
> GO
> INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
> GO
> CREATE TABLE [HelpDesk_IssueHistory] (
> [Id] [int],
> [IssueId] [int],
> [UserIdEnteredBy] [int],
> [DateEntered] [datetime]
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
> GO
> HelpDesk_Issue is a table containing Help Desk issue entries, and
> HelpDesk_IssueHistory is a table containing modification history
> records for the Help Desk issues.
> I want to write a query to retrieve values for LastUpdated, and
> LastUpdatedBy.
> LastUpdated is pretty easy. I might simply be brainfarting on not
> knowing how to do a HAVING properly, but the only way I can retrieve
> LastUpdatedBy is:
> SELECT
> LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
> Where IssueId = I.[Id]),
> LastUpdatedBy =
> (
> Select
> UserIdEnteredBy
> From
> HelpDesk_IssueHistory
> Where
> Id =
> (
> Select
> MAX(H.Id)
> From
> HelpDesk_IssueHistory H
> Where
> IssueId = I.[Id]
> )
> )
> FROM
> HelpDesk_Issue I
> My result set should be:
> Date Entered UserIdEnteredBy
> 2004-11-05 15:25:18.120 3
> This query works, but is unacceptably slow, and there's got to be a
> cleaner way of doing it.
> Thank you!
>|||george.durzi@.gmail.com wrote:
> Here is some drastically stripped down DDL for a Help Desk system I
> wrote. I only left the relevant columns, and didn't script any of the
> relationships, etc.
> CREATE TABLE [HelpDesk_Issue] ([Id] [int])
> GO
> INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
> GO
> CREATE TABLE [HelpDesk_IssueHistory] (
> [Id] [int],
> [IssueId] [int],
> [UserIdEnteredBy] [int],
> [DateEntered] [datetime]
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
> GO
> HelpDesk_Issue is a table containing Help Desk issue entries, and
> HelpDesk_IssueHistory is a table containing modification history
> records for the Help Desk issues.
> I want to write a query to retrieve values for LastUpdated, and
> LastUpdatedBy.
> LastUpdated is pretty easy. I might simply be brainfarting on not
> knowing how to do a HAVING properly, but the only way I can retrieve
> LastUpdatedBy is:
> SELECT
> LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
> Where IssueId = I.[Id]),
> LastUpdatedBy =
> (
> Select
> UserIdEnteredBy
> From
> HelpDesk_IssueHistory
> Where
> Id =
> (
> Select
> MAX(H.Id)
> From
> HelpDesk_IssueHistory H
> Where
> IssueId = I.[Id]
> )
> )
> FROM
> HelpDesk_Issue I
> My result set should be:
> Date Entered UserIdEnteredBy
> 2004-11-05 15:25:18.120 3
> This query works, but is unacceptably slow, and there's got to be a
> cleaner way of doing it.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Hmm..., Your query seems to be "saying" get the row w/ the latest date
and then get the user ID associated w/ the highest IssueHistory ID
number, which doesn't make much sense. From your limited DDL the
HelpDesk_IssueHistory ID column seems to be unnecessary (how is it an
attribute of the entity HelpDesk_IssueHistory?); therefore, that's why
your query doesn't make much sense to me.
If you just want to find the users who entered the last history item on
each issue try:
SELECT DateEntered, UserIDEnteredBy
FROM HelpDesk_IssueHistory As H
WHERE DateEntered = (SELECT MAX(DateEntered)
FROM HelpDesk_IssueHistory
WHERE IssueID = H.IssueID)
If you wanted the last entry of a specific issue use the above query as
the SQL statement in a stored procedure w/ a parameter of @.issue_id INT
and change the subquery's select clause to:
WHERE IssueID = @.issue_id
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQuPwQIechKqOuFEgEQKoIgCgyRRhsqCibrj+
zwfoQQYrlPTLkWcAoJmJ
50uIn26qiIk4AFnDVinfq+CN
=OEk/
--END PGP SIGNATURE--|||Thank you both for taking the time to reply on a Sunday.
"MGFoster" wrote:

> george.durzi@.gmail.com wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Hmm..., Your query seems to be "saying" get the row w/ the latest date
> and then get the user ID associated w/ the highest IssueHistory ID
> number, which doesn't make much sense. From your limited DDL the
> HelpDesk_IssueHistory ID column seems to be unnecessary (how is it an
> attribute of the entity HelpDesk_IssueHistory?); therefore, that's why
> your query doesn't make much sense to me.
> If you just want to find the users who entered the last history item on
> each issue try:
> SELECT DateEntered, UserIDEnteredBy
> FROM HelpDesk_IssueHistory As H
> WHERE DateEntered = (SELECT MAX(DateEntered)
> FROM HelpDesk_IssueHistory
> WHERE IssueID = H.IssueID)
> If you wanted the last entry of a specific issue use the above query as
> the SQL statement in a stored procedure w/ a parameter of @.issue_id INT
> and change the subquery's select clause to:
> WHERE IssueID = @.issue_id
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQuPwQIechKqOuFEgEQKoIgCgyRRhsqCibrj+
zwfoQQYrlPTLkWcAoJmJ
> 50uIn26qiIk4AFnDVinfq+CN
> =OEk/
> --END PGP SIGNATURE--
>|||Hey guys, sorry, still having a little trouble with this.
How would you tackle this if you couldn't guarantee that DateEntered was
unique. That's why I included the Id column in HelpDesk_IssueHistory. It's a
n
identity column, I forgot to note that on my DDL.
The query I wrote fetches the id of the latest history record, then uses
that to fetch the User who entered the records. However, it's unacceptably
slow.
Thank you
"george.durzi@.gmail.com" wrote:

> Here is some drastically stripped down DDL for a Help Desk system I
> wrote. I only left the relevant columns, and didn't script any of the
> relationships, etc.
> CREATE TABLE [HelpDesk_Issue] ([Id] [int])
> GO
> INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
> GO
> CREATE TABLE [HelpDesk_IssueHistory] (
> [Id] [int],
> [IssueId] [int],
> [UserIdEnteredBy] [int],
> [DateEntered] [datetime]
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
> GO
> HelpDesk_Issue is a table containing Help Desk issue entries, and
> HelpDesk_IssueHistory is a table containing modification history
> records for the Help Desk issues.
> I want to write a query to retrieve values for LastUpdated, and
> LastUpdatedBy.
> LastUpdated is pretty easy. I might simply be brainfarting on not
> knowing how to do a HAVING properly, but the only way I can retrieve
> LastUpdatedBy is:
> SELECT
> LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
> Where IssueId = I.[Id]),
> LastUpdatedBy =
> (
> Select
> UserIdEnteredBy
> From
> HelpDesk_IssueHistory
> Where
> Id =
> (
> Select
> MAX(H.Id)
> From
> HelpDesk_IssueHistory H
> Where
> IssueId = I.[Id]
> )
> )
> FROM
> HelpDesk_Issue I
> My result set should be:
> Date Entered UserIdEnteredBy
> 2004-11-05 15:25:18.120 3
> This query works, but is unacceptably slow, and there's got to be a
> cleaner way of doing it.
> Thank you!
>|||The reason DateEntered isn't unique is that even though I am inserting two
history records right after each other in separate db calls, I'm still
getting consecutive history records with the same datetime value.
I'm using GETDATE() within the insert sp. This isn't happening all the time,
only on about 40 of my 8000 records, but thus causing the queries you
recommended to break.
Perhaps I can handle for the server being too fast, by not using getdate,
and instead handling it on the presentation layer, and adding a time tick to
the next insert, in order to guarantee uniqueness
"George Durzi" wrote:
> Hey guys, sorry, still having a little trouble with this.
> How would you tackle this if you couldn't guarantee that DateEntered was
> unique. That's why I included the Id column in HelpDesk_IssueHistory. It's
an
> identity column, I forgot to note that on my DDL.
> The query I wrote fetches the id of the latest history record, then uses
> that to fetch the User who entered the records. However, it's unacceptably
> slow.
> Thank you
> "george.durzi@.gmail.com" wrote:
>|||Hi
Datatime is accurate one three-hundredth of a second, therefore it is
possible to get duplicates under a heavy load, although your identity
will be unique and you can (probably) use that instead and ignore the
datetime column.
e.g.
SELECT I.IssueId, I.DateEntered AS LastUpdatedBy, I.UserIdEnteredBy AS
LastUpdatedBy
FROM HelpDesk_Issue I
JOIN (Select IssueId, MAX(Id) AS LatestId From
HelpDesk_IssueHistory GROUP BY IssueId ) L ON I.IssueId = L.IssueId and
I.Id =
L.LatestId
OR
SELECT H.IssueId, H.DateEntered, H.UserIDEnteredBy
FROM HelpDesk_IssueHistory As H
WHERE H.Id = (SELECT MAX(Id)
FROM HelpDesk_IssueHistory S
WHERE S.IssueID = H.IssueID)
John|||Thanks again John, works perfectly
"John Bell" wrote:

> Hi
> Datatime is accurate one three-hundredth of a second, therefore it is
> possible to get duplicates under a heavy load, although your identity
> will be unique and you can (probably) use that instead and ignore the
> datetime column.
> e.g.
> SELECT I.IssueId, I.DateEntered AS LastUpdatedBy, I.UserIdEnteredBy AS
> LastUpdatedBy
> FROM HelpDesk_Issue I
> JOIN (Select IssueId, MAX(Id) AS LatestId From
> HelpDesk_IssueHistory GROUP BY IssueId ) L ON I.IssueId = L.IssueId and
> I.Id =
> L.LatestId
> OR
> SELECT H.IssueId, H.DateEntered, H.UserIDEnteredBy
> FROM HelpDesk_IssueHistory As H
> WHERE H.Id = (SELECT MAX(Id)
> FROM HelpDesk_IssueHistory S
> WHERE S.IssueID = H.IssueID)
> John
>

Help with a loop.

I am writing a script that will go through all the database files on a
server, collect the file sizes and return the values in a single
table. This script works for the most part, but there is an instance
when the script fails to collect the information properly. When there
are two or more data files the script only reports the first one twice.
Can someone take a look at this loop and tell me where the error is?
Thanks
-Matt-
/ ****************************************
**********
Script to calculate information about the Data Files
****************************************
**********/
DECLARE @.dbname varchar(50)
DECLARE @.string varchar(250)
SET @.string = ''
Declare @.rows int
CREATE TABLE #dbcc_showfilestats (
fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 decimal (28, 2),
UsedExtents1 decimal (28, 2),
Name varchar(50),
FileName sysname )
CREATE TABLE #dbstats (
DB_Name varchar(50),
DB_Total_Size_in_MB decimal (28, 2),
DB_Used_Size_in_MB decimal (28, 2),
DB_Free_Size_in_MB decimal (28, 2),
DB_Percent_Used decimal (28, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
-- Collects all the DB name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
INSERT #dbcc_showfilestats
EXEC (@.string)
SELECT * FROM #dbcc_showfilestats -- Debug
SELECT @.rows = count(*) from #dbcc_showfilestats
While @.rows > 0
BEGIN
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB, DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB = sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB = sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used = sum(UsedExtents1/TotalExtents1)*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
SET @.rows = @.rows - 1
END
TRUNCATE TABLE #dbcc_showfilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
SELECT * FROM #dbstats --Debug
DROP TABLE #dbstats --Debug
DROP TABLE #dbcc_showfilestats --DebugYou are selecting the same rows from #dbcc_showfilestats
every time through your 'while' loop.
Add
id int identity(1,1)
to your #dbcc_showfilestats table and change
FROM #dbcc_showfilestats
to
FROM #dbcc_showfilestats where id=@.rows|||Hi Matthew,
In addition to correctly adding a unique integer to distinguish rows in
your temp table as Mark has suggested, you may want to look at using
another temp table to loop through rather than using a cursor.
Cursors are very memory heavy in comparison to a looped through temp
table.
So instead your loop (in pseudo) would look more like:
-- SET UP 'CURSOR' TABLE
SELECT name INTO #databases FROM master..sysdatabases
-- DEFINE LOOPING PARAMETER
DECLARE @.unqName nvarchar(4000)
-- SELECT LOOPING PARAMETER
SELECT @.unqName = name FROM #databases
-- ENTER WHILE LOOP
WHILE LEN(@.unqName) > 0
BEGIN
-- PERFORM LOOP CODE
--DELETE ROW FROM LOOPING TABLE #databases
DELETE FROM #databases WHERE name = @.unqName
SELECT @.unqName = '' -- CLEAR VARIABLE
SELECT @.unqName = name FROM #databases
END
This will make a big difference in large looping scenarios - just try
it out.
Andrew La Grange
Business Artists
http://www.businessartists.co.za|||By doing the SUM(...), which is an aggregate function, you are only
saying you want 1 row.
What do you really want, the size and usage of each file? Or the size
of the entire database?
-Jeff|||If you want the entire database, then there is no need for a loop use
the following:
SELECT * FROM #dbcc_showfilestats -- Debug
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB,
DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB =
sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB =
sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used =
(sum(UsedExtents1)/sum(TotalExtents1))*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
TRUNCATE TABLE #dbcc_showfilestats

Help with a CURSOR

Here's my dilema. I'm trying to create a SQL script that will report back
to me every table that has a different row count from one database to
another. I believe the best way to accomplish this is using a CURSOR to
fetch through the tables in the DB and print those where the record count
differs. Here is what I'm trying to do:
DECLARE @.Table nvarchar(40)
DECLARE Table_CURSOR CURSOR FOR
select sysobjects.name
from sysobjects, syscolumns, systypes
where syscolumns.id = sysobjects.id and syscolumns.xtype
= systypes.xtype
and sysobjects.xtype = 'U'
group by sysobjects.name
order by sysobjects.name
OPEN Table_CURSOR
FETCH NEXT FROM Table_CURSOR
INTO @.Table
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*) FROM Database1..[@.Table] WHERE ID NOT IN (SELECT ID
FROM Database2..[@.Table])) > 1
BEGIN
Print @.Table
END
FETCH NEXT FROM Table_CURSOR
INTO @.Table
END
CLOSE Table_CURSOR
DEALLOCATE Table_CURSOR
However, I get errors on "Invalid object name 'Database1..@.Table' and
"Invalid object name 'Database2..@.Table'.
I've tried without the bracket signs around [@.Table] as well, but then get
an error "Incorrect Syntax near '@.Table'.
Any ideas? Is there an easier way to achieve this? Thanks in advance,
JasonThe problem is that you cannot substitute variables for object names (like
databases or tables). However, you can execute dynamic sql. Here is a good
article. Also, useful is the undocumented sp_msforeachtable procedure.
http://www.databasejournal.com/feat...cle.php/1438931
"Jason" <jason@.nospam.com> wrote in message
news:OTGTuNX5FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Here's my dilema. I'm trying to create a SQL script that will report back
> to me every table that has a different row count from one database to
> another. I believe the best way to accomplish this is using a CURSOR to
> fetch through the tables in the DB and print those where the record count
> differs. Here is what I'm trying to do:
> DECLARE @.Table nvarchar(40)
> DECLARE Table_CURSOR CURSOR FOR
> select sysobjects.name
> from sysobjects, syscolumns, systypes
> where syscolumns.id = sysobjects.id and syscolumns.xtype
> = systypes.xtype
> and sysobjects.xtype = 'U'
> group by sysobjects.name
> order by sysobjects.name
> OPEN Table_CURSOR
> FETCH NEXT FROM Table_CURSOR
> INTO @.Table
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(*) FROM Database1..[@.Table] WHERE ID NOT IN (SELECT ID
> FROM Database2..[@.Table])) > 1
> BEGIN
> Print @.Table
> END
> FETCH NEXT FROM Table_CURSOR
> INTO @.Table
> END
> CLOSE Table_CURSOR
> DEALLOCATE Table_CURSOR
> However, I get errors on "Invalid object name 'Database1..@.Table' and
> "Invalid object name 'Database2..@.Table'.
> I've tried without the bracket signs around [@.Table] as well, but then get
> an error "Incorrect Syntax near '@.Table'.
> Any ideas? Is there an easier way to achieve this? Thanks in advance,
> Jason
>

Help w/ recovery please

Alright, I don't know much about SQL server, this wasnt my
project, but now I'm tasked with fixing it. Yesterday
someone ran a script that dropped a bunch of tables, so
we'd like to recover to a point just before those actions
occurred.
We have current MDF and LDF files, and a backup of both
that is dated Sept 27. The LDF file format is "simple"
and is truncated to 2MB.
Is there a way to step back through the log and undo the
actions of the script? Or do we need to restore the 9.27
copy of the database and hope that the latest LDF goes
back that far? Can someone post a link or give a brief
tutorial on how to fix this? Any help would be
appreciated.The ideal course of action would have been.
12:00 Full Database Backup
15:00 Tables dropped
15:01 You were informed
15:02 Take Transaction Log Backup
15:05 Restore database from last full backup
15:10 Restore database from Transaction Log backup and use STOPAT
14:59
How do the backups you have fit with this scenario ...?
An alternative is create a new database from the last full backup you have.
Then DTS the dropped tables from the restored DB into the Live DB.
HTH
Ryan Waight, MCDBA, MCSE
"Brad V" <anonymous@.discussions.microsoft.com> wrote in message
news:0f4e01c3a865$8d738fb0$a601280a@.phx.gbl...
> Alright, I don't know much about SQL server, this wasnt my
> project, but now I'm tasked with fixing it. Yesterday
> someone ran a script that dropped a bunch of tables, so
> we'd like to recover to a point just before those actions
> occurred.
> We have current MDF and LDF files, and a backup of both
> that is dated Sept 27. The LDF file format is "simple"
> and is truncated to 2MB.
> Is there a way to step back through the log and undo the
> actions of the script? Or do we need to restore the 9.27
> copy of the database and hope that the latest LDF goes
> back that far? Can someone post a link or give a brief
> tutorial on how to fix this? Any help would be
> appreciated.|||If you had your DB in simple mode, it means you did not
have the ability to back up your transaction log and it
was continually truncated. A production system should
ideally be set to full or bulk-logged. Because of this,
point-in-time recovery is virutally impossible.
So you could restore your Sept. 27th DB to a new DB (not
the same name), and consider it your new master. There
are third party tools to trawl through the log, but since
it has been continually truncated, it will be of no use to
you most likely.
Then I would DTS or BCP out the data from the older DB and
reinsert it into the 27th database.|||Brad
By having your log in simple mode you will not be able to
recover the data. Your backup will be of the database on
Sept 27. Unless the data you want was available then,
there is no way you can recover it.
You can not step back through the log as you do not have
the log available. If you want to be able to recover you
need the database to be in full recovery mode. In this
mode you also need to backup the transaction logs
regularly.
Even if you had done this you do not step back, you step
forward. You have to load the full backup that is the
nearest before the time and date you want. You then use
the transaction log backups to roll forward to the point
in time that you are trying to reach.
Idealy you should do a full backup at least once a day.
Frequency of transaction log backups depends on the
criticality of the data. Typically they might be anything
from a couple of times a day to every five minutes (or
occasionally even more).
Sorry there is no good news this time.
Hope this helps.
John
>--Original Message--
>Alright, I don't know much about SQL server, this wasnt
my
>project, but now I'm tasked with fixing it. Yesterday
>someone ran a script that dropped a bunch of tables, so
>we'd like to recover to a point just before those actions
>occurred.
>We have current MDF and LDF files, and a backup of both
>that is dated Sept 27. The LDF file format is "simple"
>and is truncated to 2MB.
>Is there a way to step back through the log and undo the
>actions of the script? Or do we need to restore the 9.27
>copy of the database and hope that the latest LDF goes
>back that far? Can someone post a link or give a brief
>tutorial on how to fix this? Any help would be
>appreciated.
>.
>|||> Which is unlikely because it has already reached its 2MB
> limit and been truncated.
It doesn't matter, the log option is not available as the database is in
simple recovery mode, as the other has mentioned. Your closest bet to get
*something* out of the log is some tool that can work against the log, like
www.lumigent.com log explorer. However, it is likely that the log has been
truncated anyhow (regardless of any limit on file size).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Brad V." <anonymous@.discussions.microsoft.com> wrote in message
news:11bc01c3a872$fda2f840$a601280a@.phx.gbl...
> Ok, so I need to create a transaction log backup, restore
> the 9-27 database (probably to a new name), then apply the
> log and hope that it goes back far enough to include all
> data entered since then (stopping just before the snafu).
> Which is unlikely because it has already reached its 2MB
> limit and been truncated. (I'll have to double-check that
> it was indeed limited, and not just coincidence that its
> at 2048KB)
> Thank you all for your help, I just started learning SQL
> server last night. I'll try this when I get home from
> work.
>
> >--Original Message--
> >The ideal course of action would have been.
> > 12:00 Full Database Backup
> > 15:00 Tables dropped
> > 15:01 You were informed
> > 15:02 Take Transaction Log Backup
> > 15:05 Restore database from last full backup
> > 15:10 Restore database from Transaction Log backup
> and use STOPAT
> >14:59
> >
> >How do the backups you have fit with this scenario ...?
> >
> >An alternative is create a new database from the last
> full backup you have.
> >Then DTS the dropped tables from the restored DB into the
> Live DB.
> >
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"Brad V" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0f4e01c3a865$8d738fb0$a601280a@.phx.gbl...
> >> Alright, I don't know much about SQL server, this wasnt
> my
> >> project, but now I'm tasked with fixing it. Yesterday
> >> someone ran a script that dropped a bunch of tables, so
> >> we'd like to recover to a point just before those
> actions
> >> occurred.
> >>
> >> We have current MDF and LDF files, and a backup of both
> >> that is dated Sept 27. The LDF file format is "simple"
> >> and is truncated to 2MB.
> >>
> >> Is there a way to step back through the log and undo the
> >> actions of the script? Or do we need to restore the
> 9.27
> >> copy of the database and hope that the latest LDF goes
> >> back that far? Can someone post a link or give a brief
> >> tutorial on how to fix this? Any help would be
> >> appreciated.
> >
> >
> >.
> >|||Ok, so I need to create a transaction log backup, restore
the 9-27 database (probably to a new name), then apply the
log and hope that it goes back far enough to include all
data entered since then (stopping just before the snafu).
Which is unlikely because it has already reached its 2MB
limit and been truncated. (I'll have to double-check that
it was indeed limited, and not just coincidence that its
at 2048KB)
Thank you all for your help, I just started learning SQL
server last night. I'll try this when I get home from
work.
>--Original Message--
>The ideal course of action would have been.
> 12:00 Full Database Backup
> 15:00 Tables dropped
> 15:01 You were informed
> 15:02 Take Transaction Log Backup
> 15:05 Restore database from last full backup
> 15:10 Restore database from Transaction Log backup
and use STOPAT
>14:59
>How do the backups you have fit with this scenario ...?
>An alternative is create a new database from the last
full backup you have.
>Then DTS the dropped tables from the restored DB into the
Live DB.
>
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Brad V" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f4e01c3a865$8d738fb0$a601280a@.phx.gbl...
>> Alright, I don't know much about SQL server, this wasnt
my
>> project, but now I'm tasked with fixing it. Yesterday
>> someone ran a script that dropped a bunch of tables, so
>> we'd like to recover to a point just before those
actions
>> occurred.
>> We have current MDF and LDF files, and a backup of both
>> that is dated Sept 27. The LDF file format is "simple"
>> and is truncated to 2MB.
>> Is there a way to step back through the log and undo the
>> actions of the script? Or do we need to restore the
9.27
>> copy of the database and hope that the latest LDF goes
>> back that far? Can someone post a link or give a brief
>> tutorial on how to fix this? Any help would be
>> appreciated.
>
>.
>

Help shrinking database doesnt work..

On ower server (ms sql 2000) we have 5 databases who have to much unused space allocated. I all ready tried with the following script to shrink the database and release the allocated unused space to the Operating System. But it doesn't work. :confused: What I'm I doing wrong? :mad:

backup log [public] with no_log
DBCC shrinkdatabase ( [public],0,truncateonly)
dump transaction [public] with no_log

GO

Please, Help Metry with this command on query analyzer
I hope this will solve ur problem

to get more explanation on this pl go through the help menu.

EXEC sp_dboption 'databasename', 'trunc. log on chkpt.', 'TRUE'

pl inform wheather it is working or not|||Originally posted by natas
On ower server (ms sql 2000) we have 5 databases who have to much unused space allocated. I all ready tried with the following script to shrink the database and release the allocated unused space to the Operating System. But it doesn't work. :confused: What I'm I doing wrong? :mad:

backup log [public] with no_log
DBCC shrinkdatabase ( [public],0,truncateonly)
dump transaction [public] with no_log

GO

Please, Help Me

I do not know whether your log file or data file is the problem. usually if the data file is larger than what you require ..
a checkdb followed by shrinkdatabase should take care of it..

if your log file is the problem then try shrinkfile instead of shrinkDB.. moreover make sure that you have no open transactions.. or if you are replicating your distributor is working.. before you try this.. even a lot of views ,being constantly used can cause the shrink to fail..

wish you luck..|||Originally posted by natas
On ower server (ms sql 2000) we have 5 databases who have to much unused space allocated. I all ready tried with the following script to shrink the database and release the allocated unused space to the Operating System. But it doesn't work. :confused: What I'm I doing wrong? :mad:

backup log [public] with no_log
DBCC shrinkdatabase ( [public],0,truncateonly)
dump transaction [public] with no_log

GO

Please, Help Me|||check with ur tempdb size if it is comparativly larger in size right click select shrinkfile both (temp log and tempdata ) select compress pages and then truncate free space from the file.

have u observed with any temporary cursors tables etc u have created for manipulation and forgot to close it.

have u selected autoshrink option .

if possible observe with the table's if index is corupted or fragmented to check this u can use dbcc showcontig option and observe scan density%.

if it is not nearing 100% u can run dbcc indexfrag for de-fragmenting the table this is related with tr log file.

reply me the result.|||<quote>
by vishy
I do not know whether your log file or data file is the problem. usually if the data file is larger than what you require ..
a checkdb followed by shrinkdatabase should take care of it..

if your log file is the problem then try shrinkfile instead of shrinkDB.. moreover make sure that you have no open transactions.. or if you are replicating your distributor is working.. before you try this.. even a lot of views ,being constantly used can cause the shrink to fail..

wish you luck..
</quote>

The problem is the data file i wil try out you're solution. Thnks.

I will reply soon.

Help setting up Peer-to-peer replication for approximately 500 db'

We are looking for a script that will setup Peer-to-peer replication for
approximately 500 databases.
Obviously the Wizard will do it, but it would be extremely time consuming.
And, after setting up the publication you would still have to setup the
Peer-to-Peer topology.
Is there a simple way script the entire process?
peer-to-peer is only really scalable to 10 or so nodes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jarek Gal" <Jarek Gal@.discussions.microsoft.com> wrote in message
news:916ACC30-728E-4AA6-9D7D-0CDA7DD50407@.microsoft.com...
> We are looking for a script that will setup Peer-to-peer replication for
> approximately 500 databases.
> Obviously the Wizard will do it, but it would be extremely time consuming.
> And, after setting up the publication you would still have to setup the
> Peer-to-Peer topology.
> Is there a simple way script the entire process?
|||We have 2 SQL servers but need to do that 500 times. So the 10 nodes is not
an issue.
"Hilary Cotter" wrote:

> peer-to-peer is only really scalable to 10 or so nodes.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Jarek Gal" <Jarek Gal@.discussions.microsoft.com> wrote in message
> news:916ACC30-728E-4AA6-9D7D-0CDA7DD50407@.microsoft.com...
>
>
|||Once you've set it up for one node, scripting it out and amending the
scripts for each subsequent node shouldn't be too difficult:
http://www.replicationanswers.com/Script3.asp. You'll still need to get the
backup files restored on each node before commencing though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .