Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2010
    Posts
    19

    Unanswered: Remove Unallocated space & Server level audit

    Dear All,

    1. How can I quickly remove unallocated space from a SQL Server database?
    2. How can I do server level audit if any table is dropped in the SQL Server?

    Thanks in advance
    G Arunagiri

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    1. Do you mean shrink the database file?
    2. Do you want to prevent tables being dropped? Or allow them to be dropped but record when and who dropped them? Or something else?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2010
    Posts
    19
    Hi,

    1.
    dbcc shrinkdatabase('DBNAME') command takes more time to execute.
    how to quickly remove Unallocated space.
    2.
    Allow them to be dropped but record when and who dropped the tables in the server.

    Thanks in advance
    G Arunagiri

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    1) DBCC SHRINKFILE is probably more what you want. It takes time to execute because pages have to be moved within the file. This has other bad consequences. Paul Randal tried, I think, to have these commands removed from SQL Server. If this is a regular "maintenance" routine then stop - you are doing it wrong. Have a look at Paul Randal's blog for his thoughts on shrinking databases & logs.
    2) Create a DDL trigger and log the data in to a table. I have not used it but CDC might be useful too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2010
    Posts
    19
    Hi,
    1.How to track who updated the tables and when it was happend.

  6. #6
    Join Date
    Oct 2011
    Posts
    29
    CDC is what you'll need. The other way to do is to create update triggers on all tables. Whenever any table is updated write the entries in a log table.

  7. #7
    Join Date
    Jul 2010
    Posts
    19
    Hi,
    how to find modified user login id in CDC? Thank you for yor quick support.
    Thanks
    GKA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •